if function multiple variables

C

cin

Please advise why the following formula returns the FALSE word, eve
when the entered amount (D8 equal to 650000) meets one of th
arguments:

=SI(D8<450000,(D8*0.7%),SI(D8>=450000<600000,(D8*0.65%),SI(D8>=600000<750000,(D8*0.6%),SI(D8>=750000<900000,(D8*0.55%),SI(D8>900000,(D8*0.5%))))))

I have tried everything, please hel
 
S

swatsp0p

cin said:
Please advise why the following formula returns the FALSE word, even
when the entered amount (D8 equal to 650000) meets one of the
arguments:

=SI(D8<450000,(D8*0.7%),SI(D8>=450000<600000,(D8*0.65%),SI(D8>=600000<750000,(D8*0.6%),SI(D8>=750000<900000,(D8*0.55%),SI(D8>900000,(D8*0.5%))))))

I have tried everything, please help
Click to expand...

cin: Your formula is incorrect. It should read:

=SI(D8<450000,(D8*0.7%),SI(AND(D8>=450000,D8<600000),(D8*0.65%),SI(AND(D8>=600000,D8<750000),(D8*0.6%),SI(AND(D8>=750000,D8<900000),(D8*0.55%),SI(D8>900000,(D8*0.5%))))))

This returns 3900 for a value of 650000 (650000*.6%=3900)

note: you may have to translate the AND function to your language.
Sorry.

HTH

Bruce
 
J

JE McGimpsey

There's no need to use the AND() statements - the second SI(...) will
only be executed if D8>=450000:

=D8*SI(D8<450000,0.7%,SI(D8<600000,0.65%,SI(D8<750000,0.6%,SI(D8<900000,0
..55%,0.5%))))

You could also use something like:

=D8*LOOKUP(D8,{0,450000,600000,750000,900000},{0.7,0.65,0.6,0.55,0.5})
 
Top