Excel Formula for sliding scale commission

[jlr123]

Need a formula to bump commission according to the attached sheet. Your assistance is greatly appreciated.

 

[SkipVought]

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]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jlr123]

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,

 

[SkipVought]

Check this out.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jlr123]

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.

 

[SkipVought]

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]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jlr123]

Yes, that is what I was told.

 

[SkipVought]

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]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jlr123]

No. my original workbook shows $232.28. as the additional over base.

 

[SkipVought]

Your new workbook solution

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

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

 

[jlr123]

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.

 

[SkipVought]

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]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

Modified formula in G5 from your first post:
=(B3-VLOOKUP(B3,$B$6:$D$9,1))*VLOOKUP(B3,$B$6:$E$9,4)

combo

 

[SkipVought]

@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]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

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]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jlr123]

Thank you Skip. I appreciate your assistance!

 

[bluedragon2]

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...