Need to do multiple IF's
Posted by Jon Swanson on February 13, 2002 11:54 AM
Thanks Aladin, but I need to do multiple one ie 2000 below equal to b4, 2000-5000 equal to b5, 5000 or greater equal to b6
Posted by Aladin Akyurek on February 13, 2002 12:04 PM
We still comparing B20 I suppose.
=(B20 < 2000)*B4+(B20 >= 2000)*(B20 < 5000)*B5+(B20 >=5000)*B6
Jon, this formula is as good as one with IFs.
Aladin
Posted by David Mehl on February 13, 2002 1:19 PM
=IF(B20<2000,B4,IF(B20>=1999,IF(B20>5000,B6,B5)))
Posted by Clifford Phillips on February 13, 2002 4:54 PM
Here is your answer:
=IF(B20>=5000,B6,IF(B20>=2000,B5,B4))
But I think you wanted to know how to make them yourself! You do it one IF statement at a time! Also it is some times better to rearrange the logic before you start. In this case B20 >= 5000 use B6, B20>=2000 use B5,use B4 . Now to do it, select the cell where you want the formula to be hit =, at the top select IF button, put B20>= 5000 in the logical_test box, Put B6 in the true box, and (0) in the false box click ok. Now at the top of excel you see =IF(B20>=5000,B6,0) I click right next to the 0 and delete it, Excel knows you want to change the formula, so you click on the IF button again,you are now going to put the second IF statement inside the first. Do it the same way as before, B20>=2000 in first box,B5 in the second box and B4 in the third box.
PS: Aladin, If Jon uses Text in B4-B6 your solution will not work.
Posted by Aladin Akyurek on February 13, 2002 10:04 PM
Clifford --
I'd rather learn the syntax of functions and try to devise formulas myself than ask the paperclip or the function wizard.
> If Jon uses Text in B4-B6 your solution will not work.
Then you'll get an error that informs you the cells of interest fail to obey the expected data type. If these cells are text-formatted, you can manipulate these cells such that they become numeric or coerce them in formulas to behave as numbers by doing, e.g.:
=(B20 > 0.786549) * (B4+0)
