Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Formula for sliding scale commission

Status
Not open for further replies.
Hi,

Why are the last two values in your rate table identical ($127,815.00)?

AND...

...you only provide example formulas (in other words, how YOU think it ought to be calculated) with what the end result should be. HOWEVER, you have not provided the business rules for arriving at a desired value.

Need the business rules.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
AD1D550E-FCB5-4AB6-A3C8-1F86CACE2554_ne1bcz.jpg
 
Thanks for responding.
Total Sales = 118,137.51

1. Anything over 127,816 will only get 3% commission

2. Rule is from 0-87,813 = 2% if over 87,813 but = <107,814 they will receive 2.25% on 107,814 (2,425.81), if >107,814 but <127,815 they will receive an additional commission of 2.5% on the difference of 118,137,.51 and 107,814 (which would be 10,323.51* 2.5% =258.08 total commission for this sale is 2425.81+258.08= 2,683.89.

It is difficult to explain when written. Hope you get the gist of what I am saying.
Thanks,
 
This does not work. the total commission on $118,137.51 should be $2,689.40
Revenue - $118,137.51
Tier Max - 107,814.00 @ 2.25% = $2,425.82
Difference over Tier = $10,323.51 @ 2.5% = $263.58
Total Commission = $2425.82+$263.58 = $2,689.40
Thanks.
 
So you're saying that if the revenue, for instance is greater than $127,815, they are earning 2.5% on the entire $127,815 and an additional 3% on the incremental over $127,815?

That's unusual in my experience
(which, along with $2.50, might get you a cup of coffee🤓)

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
AD1D550E-FCB5-4AB6-A3C8-1F86CACE2554_ne1bcz.jpg
 
Yes, that is what I was told.
 
Difference over Tier = $10,323.51 @ 2.5% = $263.58

Actually = $258.09 NOT $263.58

So maybe your original workbook was right???


Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
AD1D550E-FCB5-4AB6-A3C8-1F86CACE2554_ne1bcz.jpg
 
No. my original workbook shows $232.28. as the additional over base.
 
Your original workbook shows $232.28, which is $10,323.51*2.25%. If everything else is ok, just extend searched range by 4th column and pick value from it (second term in output formula, take "over tier" value instead).


combo
 
Skip,
The table is not giving me the correct commission.If Revenue = 118,137.51 then base commission is $2425.82 and over tier would be $258.09.
0-8713 = 2%
8714-107,814 =2.25% This is the breakpoint for the 118,137.51 anything between 107814 and 118137.51 (10,323.51) would be AT 2.5%
107815-127815 = 2.5%
I know this is crazy but that is one of the formulas that Management wants.

Combo,
Please send your table with formula so I can see what you are talking about.
Thanks for your assistance.
 
The table is not giving me the correct commission.If Revenue = 118,137.51 then base commission is $2425.82 and over tier would be $258.09.

This is exactly what is being calculated. You can SEE it in the sheet in column H. If it's off by a penny, then YOU figure out how you want the rounding to be calculated. This isn't a math class with Mister jlr marking my quiz!

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
AD1D550E-FCB5-4AB6-A3C8-1F86CACE2554_ne1bcz.jpg
 
Modified formula in G5 from your first post:
=(B3-VLOOKUP(B3,$B$6:$D$9,1))*VLOOKUP(B3,$B$6:$E$9,4)

combo
 
@combo, its gonna take more that that.

If you enter a value less than $87,813.00 you get an error the table is mis-defined to begin with. There need to be a previous row starting with $0.00 and the Earned and Rate values repositioned to work with GREATER THAN lookup logic. That's why I juggled my formulas AND shifted his table.


Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
AD1D550E-FCB5-4AB6-A3C8-1F86CACE2554_ne1bcz.jpg
 
How about this?

I reformatted the table to include a row for $0.

Used Named ranges based on Data in your sheet.

Modified the 4 formulas in Explanation of Service Commission Earned Below:
[tt]
F4: =INDEX(Commission_Earned,MATCH(Service_Revenue,Goal_Value,1))
F5: =Service_Revenue-INDEX(Goal_Value,MATCH(Service_Revenue,Goal_Value,1))
G5: =INDEX(Commission_Rate,MATCH(Service_Revenue,Goal_Value,1)+1)*F5
F6: =F4+G5
[/tt]



Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
AD1D550E-FCB5-4AB6-A3C8-1F86CACE2554_ne1bcz.jpg
 
 https://files.engineering.com/getfile.aspx?folder=28111d3b-a474-4390-b6d8-b6cfb38f5e7b&file=Formula_Needed_copy.xlsx
Thank you Skip. I appreciate your assistance!
 
I didn't look at any of the workbooks, but I did create a table (a1:c5) :

0 2% 0
87,813 2.25% 0
107,814 2.25% 2.50%
127,815 2.25% 2.50%
999,999 2.25% 3%

And put the sales amount in F1 and used the formula:

=VLOOKUP(F1,A1:C5,1,1)*VLOOKUP(F1,A1:C5,2,1)+((F1-VLOOKUP(F1,A1:C5,1,1))*VLOOKUP(F1,A1:C5,3,1))


[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top