Commission Calculation with IF Stmt.

[Shams]

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 Carey]

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

[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.
> >