Help!! IF Formula
Posted by Penfold on February 08, 2001 8:58 AM
i need an IF formula that checks a cell for a value in between three different values and then proform a simple calculation heres what i got
=IF(B16>=10000<15001,B16*0.001,IF(B16>=15001<25001,B16*0.0015,IF(B16>=25001<50001,B16*0.002,IF(B16>=50001,B16*0.0025))))
but this only works if my value is over 50000??
any help
thanX Penfold
Posted by Mark W. on February 08, 2001 9:24 AM
=B16*VLOOKUP(B16,{10000,0.001;15001,0.0015;25001,0.002;50001,0.0025},2)
Posted by Aladin Akyurek on February 08, 2001 9:33 AM
Try:
=IF(AND(B16>=10000,B16<=15000),B16*0.001,IF(AND(B16>15000,B16<=25000),B16*0.0015,IF(AND(B16>25000,B16<=50000),B16*0.002,IF(B16>50000,B16*0.0025,""))))
Look also at VLOOKUP under Help to see if you can use it instead of IF.
Aladin
Posted by Mark W. on February 08, 2001 9:44 AM
Aladin, I thought you said in a previous posting
that you don't recommend more than 3 nested IFs.
Posted by Aladin Akyurek on February 08, 2001 10:06 AM
I still don't. That's why I suggested Penfold looking at (ordinary use of) VLOOKUP. Also felt that getting the relational operators right should be the right thing to do here.
Aladin
Posted by Penfold on February 09, 2001 12:05 AM
Thanx
Thank you that works fine, i will have a look VLOOKUPS to see if it can do what i want.
Thanx
Penfold
