Nested IF Functions and Conditional Functions

Nested IF Functions Sometimes you can’t resolve a logical problem using only logical operators and the AND, OR, and NOT functions. In these cases, you can nest IF functions to create a hierarchy of tests. For example, the formula =IF(A1=100, "Always", IF(AND(A1>=80, A1<100), "Usually", IF(AND(A1>=60, A1<80), "Sometimes", "Who cares?"))) states, in plain language, the following: If the value is 100, return Always; if the value is from 80 through 99, return Usually; if the value is from 60 through 79, return Sometimes; or if none of these conditions is true, return Who cares?. You can create formulas containing up to 64 levels of nested functions. Other Uses for Conditional Functions You can use all the conditional functions described in this section as stand-alone formulas. Although you usually use functions such as AND, OR, NOT, ISERROR, ISNA, and ISREF within an IF function, you can also use formulas, such as =AND(A1>A2, A2<A3), to perform simple conditional tests. This formula returns the logical value TRUE if the value in A1 is greater than the value in A2 and the value in A2 is less than the value in A3. You might use this type of formula to assign TRUE and FALSE values to a range of numeric database cells and then use the TRUE and FALSE conditions as selection criteria for printing a specialized report.

NESTED IFs Examples Excel allows the "value-if-true" and "value-if-false" to contain IF statements. These are called Nested IFs. Microsoft Excel allows up to 64 nested IF functions as the values of an IF function. Here's an easy-to-understand example::=IF(B9>=18,"Adult",IF(B9>12,"Teen","Child"))     This function evaluates the value in cell B9. If the value in B9 is ≥18, the logical test is true, so Excel displays Adult and stops reading the function. If the value is not ≥18, Excel executes the "value-if-false" argument. This argument contains an IF statement testing to see if the value in B9 is >12. If true, Excel returns a value of Teen. If not true, Excel displays Child in the cell.We see many examples on the Internet where the AND function is added to the second IF statement. Instead of B9>12, they code AND(B9>12,B9<18).This is not necessary! The only reason Excel has progressed to this second IF statement, the value-if-false argument, is because the logical test was false (B9 was not = or > 18!) So why needlessly test for it again!Now let's study two more complicated examples:Example 1: Review the worksheet below and study the IF function in the worksheet's formula bar. Each team plays two rounds and their scores are added. If their total score is 12 or greater (> 11), they get a Gold medal. If the total score is between 8 and 11 (>7 but < 12), they are awarded a Silver Medal. If 7 or less, they receive no prize.Because we are working with Integers in this example, instead of saying ≥12, we can just say > 11 and so on. However, if decimal points were applicable, this function would have to read:=IF((C3+D3)>=12,"Gold",IF((C3+D3)>=8,"Silver","No Prize"))Hint: Always make your functions as simple as possible.Example 2: The traditional application of nested IFs is in the classroom. But this function construct can be used in any application where grades or labels are awarded based on a numeric or alphabetical scale.In this example, when the students' scores are entered into the worksheet, Excel displays the appropriate letter grade for each student. Feel free to copy and paste this formula into your worksheet. Don't forget to change cell B4 to the cell in your worksheet containing the first student's numeric score. Then copy and paste the function down the column for the other students.=IF(B4>89,"A",IF(B4>79,"B",IF(B4>69,"C",IF(B4>59,"D","F"))))Nested IF functions make some people crazy, so let's take a step-by-step look at how Excel interprets this expression. STEP 1 If B4 is > 89, then the test is true and Excel displays A in the cell and quits processing. If not, Excel proceeds to the "if not true" argument which is underlined in purple in the image below. STEP 2 Excel encounters an IF function. If B4 is > 79, it displays a B in the cell and finishes processing. If not, Excel continues on to the "if not true" argument of the IF function as underlined in purple as shown below. STEP 3 Once again, Excel encounters an IF function. If B4 is > 69, then the logic test is true, Excel displays a C in the cell, and quits. If not, Excel processes the "if not true" argument underlined in purple. STEP 4 Finally(!)...as Excel processes this argument, it encounters an IF function. If B4 is > 59, then the test is true and Excel displays a D in the cell. If not, Excel displays an F in the cell and processing is completed.When working with nested IF statements, it is often useful to write the formula in a simple editor like Notepad (not a word processing program like Word) in the format shown below in order to keep track of the parentheses. We've color-coded ours to help you see which pairs belong together. But remove the spaces before copying and pasting into a spreadsheet cell. =IF(B4>89,"A",     IF(B4>79,"B",       IF(B4>69,"C",         IF(B4>59,"D","F"))))

Page 1