Monday, February 1, 2010

How do I create nested IFs formula or an IF in conjection with OR function?

IF/OR

To create a Microsoft Excel formula with an IF and an OR statement read the following example:

=IF(OR(E15=“ALUMINUM 2117”,E15=“STEEL ALLOY”), ”CADIUM PLATE”, ””)


If the text of cell E15 is exactly “ALUMINUM 2117” OR “STEEL ALLOY”, then the selected cell will display “CADIUM PLATE”, otherwise no value will be displayed because of the null set, or empty quotations at the end.

Nested IFs

You may need a formula that involves several IF statements in one. We call these IFs inside IFs Nested IFs (that's a lot of IF's!) See these examples:


=IF(C2=2,0.062, IF(C2=3, 0.094, IF(C2=4, 0.125, "")))

Explanation: If the value in cell C2 is equal to 2, then the dimension 0.062 will be displayed, if the cell C2 is equal to 3, 0.094 will be displayed, if cell C2 is 4, then 0.125 will be displayed, otherwise nothing will be displayed.
Make sure the number of left parentheses equals the number of right parentheses (color coded in Excel). Also note that Microsoft Excel 97-03 only allows users to have 7 levels of nested Ifs.

Other examples:

=IF(U17="CRES 303", "C", IF(U17="CRES A286", "CA", IF(U17="ALLOY STEEL", "-", "")))

=IF(C2=2,0.062, IF(C2=3, 0.094, IF(C2=4, 0.125, IF(C2=5, 0.156, IF(C2=6, 0.187, IF(C2=8, 0.25, ""))))))

Stay tuned for more!

Please feel free to ask any questions.