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:
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...
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
($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 -
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.
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
=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:
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.
MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on
this web site are provided "as is" and we do not guarantee that they can be used
in all situations.
Access®, Excel®, FrontPage®, Outlook®, PowerPoint®, Word® are registered
trademarks of the Microsoft Corporation.
MrExcel® TM is a registered trademark of Tickling Keys, Inc.
All contents © 1998-2014 by MrExcel Consulting | All rights reserved