Commission Calculation with IF Stmt.
 

Folks,
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.

Assume your sales amount is in A1 and the table, including headings, is in
C1:E4

The 3 formulas would be:

=IF(A1=C2,D2*MIN(A1,C3-C2),0)
=IF(A1=C3,D3*MIN(C4-C3,A1-C3),0)
=IF(A1=C4,D4*(A1-C4),0)



"Shams" wrote:

Folks,
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.

Duke,
Thank you very much for your excellent tip. I think it is working like a
charm!! I'll play around with the numbers a little bit more to see if
everything is good to go. Thanks.

Regards,
Shams.


"Duke Carey" wrote:

Assume your sales amount is in A1 and the table, including headings, is in
C1:E4

The 3 formulas would be:

=IF(A1=C2,D2*MIN(A1,C3-C2),0)
=IF(A1=C3,D3*MIN(C4-C3,A1-C3),0)
=IF(A1=C4,D4*(A1-C4),0)



"Shams" wrote:

Folks,
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.