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.