EXCEL -IF,THEN etc help ?

C_Mababejive

Hi all, i am getting confused by nested IF statements..

I have a value that appears in E34 which varies as a result of other calculations

I want to compare that result in E34 and output a result in B37 based on the following..

E34

<=0.5 B37=2
>0.5 AND <=0.8 B37=3
>0.8 AND <=1 B37=4


I think this can be done with a nested IF statement but im not sure if there is a formula template to help with complex IFs ?

Thanks

[Deleted User]

=IF(E34<=0.5,2,IF(AND(E34>0.5, E34<=0.8),3,IF(AND(E34>0.8, E34<=1),4)))

There you go. In each nested IF statement, you put the value you want outputted into the 'true' section, and in the 'false' section you put the next IF statement

e.g. If you need to account for values over 1...

=IF(E34<=0.5,2,IF(AND(E34>0.5, E34<=0.8),3,IF(AND(E34>0.8, E34<=1),4,IF(E34>1, 5)))

Split the clauses out, make sure they work, then chain them together

C_Mababejive

Thanks for the quick reply Ringo,, have been searching on line tutorials and just got blinded by it all ! I shall use your reply to understand how it works

[Deleted User]

If it helps, here are the three separate IFs

=IF($A$1<=0.5,2,)
=IF(AND($A$1>0.5, $A$1<=0.8),3)
=IF(AND($A$1>0.8, $A$1<=1),4)

Here's the chain..

=IF(E34<=0.5,2,IF(AND(E34>0.5, E34<=0.8),3,IF(AND(E34>0.8, E34<=1),4)))

You could always form that as...

=IF(E34<=0.5, 2,
IF(AND(E34>0.5, E34<=0.8), 3,
IF(AND(E34>0.8, E34<=1), 4)
)
)

If you need help making sure there's the right number of brackets, try getting a programmers editor like notepad++

verybigchris

You can do a shorter version without the ANDs:

=IF(E34<=0.5,2,IF(E34<=0.8,3,IF(E34<=1,4)))

The second IF statement is only reached if E34 is greater than 0.5, so any test for E34>0.5 at that point is tautological.

thescouselander

Nested if statements are the devil's work. Better to use a case statement as part of a user defined function action IMO.

Alternatively, if you have the latest version of excel, you could use the IFS function.

Heedtheadvice

You just beat me to that scouse!
Nested formulas are not the easiest of thing to work with and even worserer to understand when you come back to them later to try and audit or fault find on them. By no means impossible but better to make life easy in the first place!
1 use meaningfull cell (or range names) /in the example we could hold the desired test value in cells 0, 0.5 and 1 and name them as low limit, middle value and high limit (or something similar)
2 the function does not need to be nested but can be held in (in this case) three separate cells
3 if using excel 2007 onwards each bit of the nested if function can be defined separately (using defined name, and the definition is a function!) and then combined in the 'answer cell'

For the latter consider
:
=IF(OneToSix,OneToSix,SevenToThirteen) a simple if statement held in the cell with the same logic (test,true answer,false answer)
(Obviously for different values to OP question but very understandable!) Great explanation at c.pearson.com/excel/nested.htm

Not only does separation (rather than lots of nesting) make it easier to understand each part can be separately proven and tested against unexpected results - consider Ringo's addition of '5' in the last of the nested ifs. That copes with a perhaps unexpected value to test and 5 could easily be replaced with "oops" or "out of test range" etc. Equally what would be the result of the nested IF statements if the cell contained an error code or was simply blank? ....just food for thought......

C_Mababejive

What we occasional users really need is a simple on line/ or software tool to automate it having the ability to put various logical operators in it ..