NESTED IF STATEMENT

[SSJ]

Hello,

I have to calculate commission on sales tax collected based on the following criteria:

1) If sales tax collected is $11.00 or less, then the commission = to the sales tax collected
2) If sales tax collected is $11.01 - $333.33, then the commission= $11.00
3) If sales tax collected is more than $333.33, then commission = 3.3% of the sales tax collected, with a maximum amount = $99.00

My attempt was as follows, which work, however, I want to find out a better way, if any, to do this:

=IF(A2<=11,A2,IF(A2<=333.33,11,IF(A2<3000,A2*0.033,99)))

Thanks
SJ

 

[Don Guillett]

Based on what you said???

=IF(a2>333.33,99,IF(a2>11,11,a2))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hello,

I have to calculate commission on sales tax collected based on the following criteria:

1) If sales tax collected is $11.00 or less, then the commission = to the sales tax collected
2) If sales tax collected is $11.01 - $333.33, then the commission= $11.00
3) If sales tax collected is more than $333.33, then commission = 3.3% of the sales tax collected, with a maximum amount = $99.00

My attempt was as follows, which work, however, I want to find out a better way, if any, to do this:

=IF(A2<=11,A2,IF(A2<=333.33,11,IF(A2<3000,A2*0.033,99)))

Thanks
SJ

 

[David Biddulph]

=IF(A2<=11,A2,IF(A2<=333.33,11,MIN(A2*3.3%,99)))
--
David Biddulph

Hello,

I have to calculate commission on sales tax collected based on the following
criteria:

1) If sales tax collected is $11.00 or less, then the commission = to the
sales tax collected
2) If sales tax collected is $11.01 - $333.33, then the commission= $11.00
3) If sales tax collected is more than $333.33, then commission = 3.3% of
the sales tax collected, with a maximum amount = $99.00

My attempt was as follows, which work, however, I want to find out a better
way, if any, to do this:

=IF(A2<=11,A2,IF(A2<=333.33,11,IF(A2<3000,A2*0.033,99)))

Thanks
SJ

 

[SSJ]

Don thanks for your response.

There is a cap on the criteria # 3.

In other words, if A2 is greater than $333.33 then A2 needs to be multiplied by 3.3%, however, the result of this multiplication cannot exceed $99.

For example, if A2 = $3500, then 3.3% of 3500 is equal to $115.50, however, due to the capping, the commission will not be $115.50 but instead it will be $99.00

Regards
SJ
Based on what you said???

=IF(a2>333.33,99,IF(a2>11,11,a2))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hello,

I have to calculate commission on sales tax collected based on the following criteria:

1) If sales tax collected is $11.00 or less, then the commission = to the sales tax collected
2) If sales tax collected is $11.01 - $333.33, then the commission= $11.00
3) If sales tax collected is more than $333.33, then commission = 3.3% of the sales tax collected, with a maximum amount = $99.00

My attempt was as follows, which work, however, I want to find out a better way, if any, to do this:

=IF(A2<=11,A2,IF(A2<=333.33,11,IF(A2<3000,A2*0.033,99)))

Thanks
SJ

 

[Don Guillett]

=IF(J13>333.33,MIN(99,0.033*J13),IF(J13>11,11,J13))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Don thanks for your response.

There is a cap on the criteria # 3.

In other words, if A2 is greater than $333.33 then A2 needs to be multiplied by 3.3%, however, the result of this multiplication cannot exceed $99.

For example, if A2 = $3500, then 3.3% of 3500 is equal to $115.50, however, due to the capping, the commission will not be $115.50 but instead it will be $99.00

Regards
SJ
Based on what you said???

=IF(a2>333.33,99,IF(a2>11,11,a2))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hello,

I have to calculate commission on sales tax collected based on the following criteria:

1) If sales tax collected is $11.00 or less, then the commission = to the sales tax collected
2) If sales tax collected is $11.01 - $333.33, then the commission= $11.00
3) If sales tax collected is more than $333.33, then commission = 3.3% of the sales tax collected, with a maximum amount = $99.00

My attempt was as follows, which work, however, I want to find out a better way, if any, to do this:

=IF(A2<=11,A2,IF(A2<=333.33,11,IF(A2<3000,A2*0.033,99)))

Thanks
SJ