sliding commission calculation

[greenscapevalley]

Five tiered commission system:

Commission Tier 1: $0.0 - 2.0 @ 20%
Commission Tier 2: $2.1 - 4.0 @ 21%
Commission Tier 3: $4.1 - 6.0 @ 22%
Commission Tier 4: $6.1 - 8.0 @ 23%
Commission Tier 5: $8.1 to infinity @ 24%

The numbers are all fake, I am just looking for the formula.

How do I put this into Excel in a formula so that all I have to do is
enter the number and know what commission is going to be?

Preferably I'd like to do this in five different cells. For example...

Say the commissionable amount is: 5.8; I'd like the sheet to show
something like this:

Commissionable Amount: 5.8

Commission Tier 1: $0.4
Commission Tier 2: $0.399
Commission Tier 3: $0.374
Commission Tier 4: $0.00
Commission Tier 5: $0.00

Total Commission: $1.173

This is driving me crazy and I am not a nube at this stuff.

Thanks for the help.
T

 

[Jzz]

dim Commision, Amount

if Amount => 2 then
Commision = 0.4
Amount = Amount - 2
end if
if Amount => 2 then
commision = Commision + 0.399
Amount = Amount -2
end if

etc...

Then put the values back into the sheet.

Good luck, Jzz

 

[Tom Ogilvy]

Shouldn't your ranges be continuous. in your example you appear to take
(4.0-2.1)*.21 = .399 this appears to exlude the sales for > 2.0 up to 2.1

in any event

Assume the commissionable amount is in C2

then for the first range

=MIN(C2-0,2-0)*0.2
=MAX(0,MIN(C2-2.1,4-2.1)*0.21)
=MAX(0,MIN(C2-4.1,6-4.1)*0.22)

follow the pattern.

 

[JE McGimpsey]

See

http://www.mcgimpsey.com/excel/variablerate.html