
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 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.
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.
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 © 19982014 by MrExcel Consulting  All rights reserved