Hi there. I'm trying to create a tiered commission structure calculator, but can't figure out how to make the formula work. I think it should be pretty simple (darn my weak excel skills):
<$500 = 5%
$501-$1000 = 8%
$1000-$1500 = 10%
>$1500 = 12%
For example, total sales of $1800 would generate $151 of commission based on:
(First $500 * 5%)+(Next $500* 8%)+ (Next $500* 10%)+ (Final $300* 12)
Any help is much appreciated!
Try the following design.
sales commission B2: SUMPRODUCT((B1>$A$5:$A$8)*(B1-$A$5:$A$8), $C$5:$C$8) C5: =B5 - N(B4)
The formula in B2 is:
SUMPRODUCT((B1>$A$5:$A$8)*(B1-$A$5:$A$8), $C$5:$C$8)
The formula in C5 is:
=B5 - N(B4)
Copy C5 and paste into C6:C8.
Last edited by joeu2004; 11-20-2018 at 01:26 AM. Reason: fix SUMPRODUCT range refs
This worked perfectly! Thanks so much for your help! I can't tell you how much time I wasted before breaking down and asking for help - you're a life saver!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks