I was writing some formulas for a tiered commission calculation recently that I thought I should post. But beyond just what the formulas do, it reminded me that I’ve never shared my ‘counting zeros’ opinion, so I’m wrapping that in with this post too.
You have a commission structure where you pay your salesmen 5% for every sale. If the sale is a particularly large one, you pay them a bonus commission – 8% for the portion of the sale that’s over $20,000. But you don’t want your salespeople getting so rich that they have enough money to quit. Nor do you want them to get an unfairly huge commission on an unusually large sale. So you have a third tier that reduces their commission to 1% for the portion of the sale that’s over $100,000.
Let’s look at the formulas for column H.
H4 =MAX(MIN(2*10^4,H2)*0.05,0)
H5 =MAX(MIN(8*10^4,H2-2*10^4)*0.08,0)
H6 =MAX(0,H2-10^5)*0.01
- In H4, Take the smaller of $20,000 and whatever is in H2 and multiply it by 5%. Then take that larger of that result or 0, just in case there’s a negative sale in there. (5% of $20,000 = $1,000)
- In H5, Take the smaller of $80,000 ($100k less $20k) and the amount that H2 exceeds $20,000 and multiply by 8%. Then take the larger of that result or zero. (8% of $80,000 = $6,400)
- In H6, Take the larger of 0 and the amount less $100,000 and multiply by 1%. (1% of $1,000 = $10)
The MIN part of the formulas in H4 and H5 make sure you don’t pay more commission on that tier than you should. The MAX part returns zero when the calculation goes negative.
About counting zeros. You may have noticed that I use terms like 2*10^4 to represent $20,000. I’m a big fan of commas, but I can’t use them in formulas (they’re kind of important for separating arguments). I picked up using scientific notation in formulas from a scientist I know and I love it. No more do I have count the zeros in
=IF(A1=25000000,600000,8000000)
to know if it’s 25 million, 2.5 million, or 250 million. Instead I write
=IF(A1=25*10^6, 6*10^5, 8*10^6)
An even better answer is to put those values in cells and refer to the cells. When they’re in cells, I can format them and use commas to count the zeros. But let’s face it, sometimes we hardcode numbers in formulas. And when I do, I’ve been using this method for larger numbers and, after a small adjustment period, it’s been great.
Yuu could consider to improve consistency using 10^-2 as well:
=(5*MIN(2*10^4,B1)+8*MIN(MAX(0,B1-2*10^4),8*10^4)+MAX(0,B1-10^5))*10^-2I thought I posted a suggestion to enhance consistency: (the use of 10^-1 and 10^-2 etc.)
=(5*MIN(2*10^4;B1)+8*MIN(MAX(0;B1-2*10^4);8*10^4)+MAX(0;B1-10^5))*10^-2It went into the spam bucket – not sure why.
I admit that I don’t actually use that notation for thousands. Only when it gets to 100k and over.
Clever use of scientific notation. As I get older and find myself cursing the designers that “made all the fonts too small” these sort of tricks are suddenly more interesting :)
I also like the use of MAX to assign a zero when the result is negative, instead of a longer and more redundant IF. Cool.
Love the counting zeros option. It’s sure to confuse my successor. Which is great…the more confusion I spread in my wake, the greater likelihood they’ll have to shell out a couple of hours of my ‘Urgent Pickle’ rate for a math lesson!
Interesting use of Max and Min instead of If. Love the logic ++++
An even smoother way of writing and not having to count zeros is to fully use the scientific notation and type 8E6 instead of 8*10^6 like in
which Excel immediately translates to
This not only saves 3 key strokes, but also makes it easy for Jeff Weir’s successor to understand it and therefore unfortunately decreases any consultancy fees ;-)
The display can be forced to display using E if the number format it set to “Scientific”
Formulas in row 4 (as well as snb’s) returns 1000 if row 2 is empty.