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(B2:D2)

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Shams" wrote in message
...
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)

"Shams" wrote:

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.