Calculating commisions to be paid

[CEB]

We pay our commissions based on the cumulative number of units sold. For
example, units sold = X. If 0<X<500, the commission to be paid is 10% of
sale. If 500<X<1000, the commission to be paid is 15% of the sale. 20% is
paid on all sales once the number of units sold is greater than 1500.



If column "A" has units sold for a single transaction, column "B" has the
cumulative total, column "C" has the amount of the sale; I have used the
following if statement in column "D" (Commission Paid) to achieve part of my
issue:



=IF(AND(B>0,B<=500),C*10%,IF(AND(B>500,B<=1000),C*15%,C*20%))



When I get to 490 units in column "B" and the next transaction is for 27
units, I would like column "D" to calculate the commission on 10 units at
10% and the remaining units at the next rate (in this example 17 units at
15%)



All suggestions are greatly appreciated.



CB

 

[Ron de Bruin]

Hi CEB

Check out this webpage
http://www.mcgimpsey.com/excel/taxvariablerate.html

 

[Norman Harker]

Hi CEB!

There's a problem with your data.

Cum sales <500 is 10%
Cum sales >500 < 1000 15%
Cum sales >1000 < 1500 ???
Cum sales >1500 20%

Also I think that life would be a lot easier if there was a column on
cumulative amount of sales with the commission based upon that column rather
than having to deduce the price per unit.

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[CEB]

Thanks for the help. This was a great explanation of the function.

 

[Ron de Bruin]

This was a great explanation of the function

I agree