×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Formula for sliding scale commission

Excel Formula for sliding scale commission

RE: Excel Formula for sliding scale commission

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Formula for sliding scale commission

(OP)
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,

RE: Excel Formula for sliding scale commission

(OP)
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.

RE: Excel Formula for sliding scale commission

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Formula for sliding scale commission

(OP)
Yes, that is what I was told.

RE: Excel Formula for sliding scale commission

Quote:


Difference over Tier = $10,323.51 @ 2.5% = $263.58

Actually = $258.09 NOT $263.58

So maybe your original workbook was right???

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Formula for sliding scale commission

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

RE: Excel Formula for sliding scale commission

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

RE: Excel Formula for sliding scale commission

(OP)
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.

RE: Excel Formula for sliding scale commission

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Formula for sliding scale commission

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

combo

RE: Excel Formula for sliding scale commission

@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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Formula for sliding scale commission

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:

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




Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Formula for sliding scale commission

(OP)
Thank you Skip. I appreciate your assistance!

RE: Excel Formula for sliding scale commission

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 Dragon

If I wasn't Blue, I would just be a Dragon...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close