Is there a formula for the the following calculation (a to d)?

I have tried the following way but not successful.
=if(a20.01,"1","0",if(a2100.01,"2","0",if(a2200 .01,"3","0".....continued
to d)

a) If the amount is greater than 0.01, than the rate is $1.00
b) If the amount is greater than 100.01, than the rate is $2.00
c) If the amount is greater than 200.01, than the rate is $3.00
d) If the amount is greater than 300.01, than the rate is $4.50
e) If the amount is greater than 400.01, than the rate is $5.50

Thank you very much for your response.

tRY:

=IF(AND(A2<100.01,A20.01),1,IF(AND(A2100.01,A2<2 00.01),2,IF(AND(A2200.01,A2<300.01),3,IF(AND(A23 00.01,A2<400.01),4.5,IF(A2400.01,5.5,0)))))


Corey....


"Silver" wrote in message
...
Is there a formula for the the following calculation (a to d)?

I have tried the following way but not successful.
=if(a20.01,"1","0",if(a2100.01,"2","0",if(a2200 .01,"3","0".....continued
to d)

a) If the amount is greater than 0.01, than the rate is $1.00
b) If the amount is greater than 100.01, than the rate is $2.00
c) If the amount is greater than 200.01, than the rate is $3.00
d) If the amount is greater than 300.01, than the rate is $4.50
e) If the amount is greater than 400.01, than the rate is $5.50

Thank you very much for your response.

hi,
i think you have it backwards.
excel always chooses the first true and 100.01 will always be higher than .01'
start with the highest value and work your way down.

=IF(A2200.01,"3",IF(A2100.01,"2",IF(A20.01,"1", "0")))

i didn't continue on to d . sorry. but you should get the idea.
Regards
FSt1
"Silver" wrote:

Is there a formula for the the following calculation (a to d)?

I have tried the following way but not successful.
=if(a20.01,"1","0",if(a2100.01,"2","0",if(a2200 .01,"3","0".....continued
to d)

a) If the amount is greater than 0.01, than the rate is $1.00
b) If the amount is greater than 100.01, than the rate is $2.00
c) If the amount is greater than 200.01, than the rate is $3.00
d) If the amount is greater than 300.01, than the rate is $4.50
e) If the amount is greater than 400.01, than the rate is $5.50

Thank you very much for your response.

You should also consider adding a (=) in the equation atoo, in case the A2
value is exactly the specified value.
"Corey ...." wrote in message
...
tRY:

=IF(AND(A2<100.01,A20.01),1,IF(AND(A2100.01,A2<2 00.01),2,IF(AND(A2200.01,A2<300.01),3,IF(AND(A23 00.01,A2<400.01),4.5,IF(A2400.01,5.5,0)))))


Corey....


"Silver" wrote in message
...
Is there a formula for the the following calculation (a to d)?

I have tried the following way but not successful.
=if(a20.01,"1","0",if(a2100.01,"2","0",if(a2200 .01,"3","0".....continued
to d)

a) If the amount is greater than 0.01, than the rate is $1.00
b) If the amount is greater than 100.01, than the rate is $2.00
c) If the amount is greater than 200.01, than the rate is $3.00
d) If the amount is greater than 300.01, than the rate is $4.50
e) If the amount is greater than 400.01, than the rate is $5.50

Thank you very much for your response.