Excel Formula for sliding scale commission
Excel Formula for sliding scale commission
(OP)
Need a formula to bump commission according to the attached sheet. Your assistance is greatly appreciated.
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS Come Join Us!Are you a
Computer / IT professional? Join TekTips Forums!
*TekTips's functionality depends on members receiving email. By joining you are opting in to receive email. Posting GuidelinesJobs 

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 TekTips Forums:
Register now while it's still free!
Already a member? Close this window and log in.
RE: Excel Formula for sliding scale commission
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
Total Sales = 118,137.51
1. Anything over 127,816 will only get 3% commission
2. Rule is from 087,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
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
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
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
RE: Excel Formula for sliding scale commission
Actually = $258.09 NOT
$263.58So 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
RE: Excel Formula for sliding scale commission
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
combo
RE: Excel Formula for sliding scale commission
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.
08713 = 2%
8714107,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%
107815127815 = 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
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
=(B3VLOOKUP(B3,$B$6:$D$9,1))*VLOOKUP(B3,$B$6:$E$9,4)
combo
RE: Excel Formula for sliding scale commission
If you enter a value less than $87,813.00 you get an error the table is misdefined 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
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_RevenueINDEX(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
RE: Excel Formula for sliding scale commission
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)+((F1VLOOKUP(F1,A1:C5,1,1))*VLOOKUP(F1,A1:C5,3,1))
Blue
If I wasn't Blue, I would just be a Dragon...