 
Calculate Tiered Commissions in Microsoft Excel  
Popular Pages 
Calculate Tiered Commissions in Microsoft ExcelI must have offered, in a newsgroup post, to have a look at Theresa's file without understanding what she wanted. I ended up feeling obligated to answer this question when I received her file in my email, but had no idea how to accomplish it. I see this question quite often, so I decided an article was definitely in order. I called Microsoft Excel MVP Bill Jelen of www.MrExcel.com and begged him to write an article—and not only that—but to write it by the next day. Here's his article, edited for content (because of course, he delivered the text in the Excel file, too). Thanks VERY much, Bill! I owe you one! Now I can answer yet another newsgroup question I often see. Bill provided three methods to perform this task, and used Theresa's actual file as our sample. Here's what he wrote: IntroductionWhen you start thinking about this in your head, you start to think that you need a bazillion IF statements, but you don't. The wonderful world of math makes it all work out just fine without the IF statements. I used a much shorter formula, which calculates faster, and is perfectly equivalent to all the big IF statements rolling around in your head. So, I was pretentious in method 3 and created something elegant but harder to understand. (Note: The editor couldn't even understand it so it was omitted.) Then, I tried to make it more like you (I'm sure he's talking about ME, not YOU, the reader) would think about it in Method 2. But still too pretentious. Finally, I backed off to using the IF function like everyone normally would. Let's start with method 1... Method 1For each level...Let's live in a fantasy world and assume that I will sell a bazillion dollars every month. For each level, I will get: (Top of Range  Start of Range) x Commission Rate for this Range So, in level 2, I am getting ($100K$75K) x 7.75% This is simple enough, right? But...We don't live in a fantasy world. I am going to sell some amount inside of one of those ranges. Maybe I will sell $89K. So, in level 2 I will be getting: ($89K$75K) x 7.75% You could write a bunch of IF statements to check to see if I ended up in the range, and in this method, that is what I do. The formula in G says that if I sold beyond the start of this range, then figure out the difference ($89K  $75K).
Finally, multiply the Amount to be calculated (G) x Commission Rate (D).
I took all of that logic from F, G, and H, and wrapped it into one big formula in E.
Method 2Maybe I will sell $89K. So, in level 2 I will again be getting ($89K$75K) x 7.75%. You could write a bunch of IF statements to check to see if I ended up within that range...But, it's just as easy to find the minimum of the two ranges: =MIN(MySales, Top of this Range) For all of the ranges that are under my range, I will get the full range (i.e., $100K$75K). For the range where I land, I will only get the portion of the range that I actually sold. The formula in F figures out the first number in ($89K  $75K).
The formula in G figures out the Sum of ($89K  $75K). When you copy this formula down to the other rows, you realize that something goes wrong in the levels above: The formula wants to take money away from me. This cannot be good. The formula in H replaces all the IF statements. Usually, you would have: =IF(MySales>ThisLevel,UseTheFormula,Use 0) Well, a quicker way to do this is to take the MAX of 0 and the calculation. If the calculation result is negative, then 0 is larger and that becomes the commission at this level.
Finally, I multiplies H x Rate.
I took all of that logic from F, G, H, and I and wrapped it into one big formula in E.
OfficeArtilces.com debuted on May 26, 2005.
More To Explore

