if function multiple variables
 

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(D8900000,(D8*0.5%))))))

I have tried everything, please help


--
cin
------------------------------------------------------------------------
cin's Profile: http://www.excelforum.com/member.php...o&userid=27088
View this thread: http://www.excelforum.com/showthread...hreadid=466040

cin Wrote:
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(D8900000,(D8*0.5%))))))

I have tried everything, please help

cin: Your formula is incorrect. It should read:

=SI(D8<450000,(D8*0.7%),SI(AND(D8=450000,D8<60000 0),(D8*0.65%),SI(AND(D8=600000,D8<750000),(D8*0.6 %),SI(AND(D8=750000,D8<900000),(D8*0.55%),SI(D89 00000,(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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=466040

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<750 000,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})



In article ,
swatsp0p
wrote:

cin Wrote:
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(D89 00000,(D8*0.5%))))))

I have tried everything, please help

cin: Your formula is incorrect. It should read:

=SI(D8<450000,(D8*0.7%),SI(AND(D8=450000,D8<60000 0),(D8*0.65%),SI(AND(D8=600
000,D8<750000),(D8*0.6%),SI(AND(D8=750000,D8<9000 00),(D8*0.55%),SI(D8900000,
(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