NESTED IF STATEMENT
 

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

NESTED IF STATEMENT
 

Based on what you said???

=IF(a2333.33,99,IF(a211,11,a2))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

NESTED IF STATEMENT
 

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

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

NESTED IF STATEMENT
 

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
"Don Guillett" wrote in message ...
Based on what you said???

=IF(a2333.33,99,IF(a211,11,a2))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

NESTED IF STATEMENT
 

=IF(J13333.33,MIN(99,0.033*J13),IF(J1311,11,J13) )

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SSJ" wrote in message ...
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
"Don Guillett" wrote in message ...
Based on what you said???

=IF(a2333.33,99,IF(a211,11,a2))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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