Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif function with multiple criteria | Excel Discussion (Misc queries) | |||
VLOOKUP Function using multiple worksheets | Excel Discussion (Misc queries) | |||
VLOOKUP Function using multiple worksheets | Excel Discussion (Misc queries) | |||
SumIf Function using multiple criteria | Excel Worksheet Functions | |||
Need Function for adding rows on multiple sheets... struggling rookie ;) Thanks. | Excel Worksheet Functions |