IF Stmt. for cumulative commissions.

[Shams]

I feel quite embarassed in asking the following question again. I thought I
had my answer via some very helpful comments but my boss wants me take
another stab at it. Here you go:

Basically, I have to calculate commissions based on Sales threshold. So, a
sum product formula works very well for this exercise ...i.e.:

Threshold Marginal Rate Diff Rate
0 0.25% 0.25%
1500 0.50% 0.25%
3000 1.00% 0.50%


A $1500 sales will give me $3.75, $1800 will give me $5.25 and $3600 will
give me $17.25 and so on.

However, I need to be able to break down how we are arriving at any total
amount based on the threshold...i.e.:



Sales up to $1500, calculate 0.25% commission Calculation Formula

Sales up to $3,000, calculate 0.50% commission Calculation Formula

Sales over $3,000, calculate 1.0% commission
Calculation Formula

Total Commission
Sum


I am not able to create a watertight formula for each of these threshold
levels. Technically, commissions for a given sales amount should be the same
under these two constructs. I think it is safe to assume that my sum product
formula works fine. So, how do I replicate its effect in discrete pieces for
the above construct.

I will really appreciate any help in this matter. Thank you.

Shams.

 

[Bernard Liengme]

I think we need to be told more about the two percentage rates and how you
get the results
1500*0.25% = 3.75; in agreement with your message
1800*0.25% = 4.50; how do you get 5.25?

 

[Dave O]

Trying to work out the math: in your example, you said sales of $3600
yields $17.25 commission. When I try to work it out I get ($3000 x
..5%) + ($600 x 1%) = $21.00 commission. How did you get $17.25?

 

[Shams]

Allright, this is how I got to my numbers:

So for Sales of $3,600, this is how the business logic is supposed to work:

For the first 1500, multiply 1500 * 0.0025 = 3.75
For the second 1500, multiply 1500 * 0.005 = 7.50
For the remainder, multiply 600 * 0.01 = 6.00

i.e. Total of : 17.25

So, if sales is 1600, then 1500 * 0.0025 + 100 * 0.005 = 4.25

For 10,000 in sales, 3.75 + 7.5+(7000*.01) = 81.25


I hope this makes sense. My sum product formula that I mentioned before
calculates the result in one cell. However, my boss wants me to show the
breakdown that makes up the total amount. i.e. what are the dynamics for the
three thresholds (0 to 1500 to 3000 to beyond)

 

[Sandy Mann]

Shams,

If I understand your needs correctly, and with A1:E1 labelled as "Sales",
"0.25%" "0.5%", "1%", "Total" and the Sales figure in A2, then in B2:E2
enter the formulas:

B2: =MIN(A2,1500)*0.0025
C2: =MAX(MIN(A2-1500,1500)*0.005,0)
D2: =MAX((A2-3000)*0.01,0)
E2: =SUM(B22)

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk