Calculate Tiered Commissions in Microsoft Excel

I 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:

Introduction

When 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 1

For 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 2

Maybe 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.