×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

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

# Excel Formula for sliding scale commission

## 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, Just traded in my OLD subtlety... for a NUance! "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,

Just traded in my OLD subtlety...
for a NUance!

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

Just traded in my OLD subtlety...
for a NUance!

"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, Just traded in my OLD subtlety... for a NUance! "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, Just traded in my OLD subtlety... for a NUance! "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,

Just traded in my OLD subtlety...
for a NUance!

"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

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.

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!