Fabulous Examples of Excel Functions:
IF and Nested IFs

This tutorial covers the Microsoft Excel function IF, and discusses the common practice of nesting IF statements. The tutorial provides examples and worksheet images to illustrate how to use this important function.

Author: Keynote Support

Advanced Excel users will like the video course Advanced Microsoft Excel 2010 . We have reviewed this course and find it very well done. An Excel Beginners course is also available, along with free video lessons online.

Let's begin. Click on a link below to go directly to the function listed:

• IF
• IF with AND
• Nested IFs

IF

One of the most useful functions in Excel is the IF function. The IF function allows you to test a condition and have one value returned if the condition is TRUE, and another value returned if the condition is FALSE. The syntax for IF is:

IF(logical-test,value-if-true,value-if-false)

The first argument is the logical test - a condition which must evaluate to either TRUE or FALSE. The second argument is the value Excel returns if the logical test evaluates to TRUE, and the third argument is the value that Excel returns if the logical test evaluates to FALSE.

The arguments value-if-true and value-if-false can contain cell references, formulas, and functions - including other IF functions as discussed in the next section, Nested IFs. Let’s look at some examples of the IF function:

• =IF(D1>26,33,44)  This Excel function checks to see if the value in D1 is greater than 26. If so, Excel returns a value of 33. If not, a value of 44 is returned.
• =IF(A5<>"","Done","Open")  This Excel function, a value of Done will be returned if cell A5 is not empty. Otherwise, a value of Open will be returned.
• =IF(D1<100,"OK","Over Budget")   This Excel formula checks to see if the value in D1 is less than 100. If so, Excel displays OK. If not, Excel displays Over Budget. Text must be wrapped in quotation marks.
• =IF(SUM(A1:A3)=50,"Slow","Fast")  This Excel formula checks to see if the sum of the contents of cells A1 through A3 equals 50. If so, Excel returns a value of Slow. If not, Fast is displayed. For more information about the SUM function, see our tutorial Microsoft Excel: The SUM and SUMPRODUCT Function.
• = IF (A2>1000,985,B2+245)  This Excel formula checks to see if the value in A2 is greater than 1000. If so, Excel returns a value of 985. If not, Excel displays the sum of B2 and 245.

IF with AND

Here is an example of an IF function that contains an AND function as part of the logical test. For more information about the AND function, see our tutorial on Microsoft Excel Functions: AND and OR.

The AND function asks Excel to test multiple conditions. If all of the conditions are met, Excel will return a value of TRUE. Otherwise, Excel will display FALSE.

In this example, students pass a course based on two different test scores. If the value in D5 is ≥ 20 AND the value in D6 is ≥ 70, we ask Excel to display Pass in the cell. Otherwise, we ask Excel to display Fail.

• =IF((AND(D5>=20,D6>=70)),"Pass","Fail")

NESTED IFs

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 is a simple example::

• =IF(B9>=18,"Adult",IF(B9>12,"Teen","Child"))  This Excel formula evaluates the value in cell B9. If ≥18, it displays the word Adult, and stops reading the formula. If the value is <18, Excel executes the IF statement in the "value-if-false" argument and determines if the value in B2 is >12. If so, it returns a value of Teen. Otherwise, it displays Child.

We see examples on the Internet where the AND function is added to the second IF statement. Instead of the second IF statement above just testing if the value in B9 is >12, they test to see if the value in cell B9 is >12 AND <18. This is not necessary. We already KNOW the value is <18 or Microsoft Excel would not have proceeded to the second IF statement!

Example 1: Review the IF function in the worksheet below. It is an example of a more complicated nested IF function. We award a Gold medal for a score of 12 or greater, and a Silver medal for a score between 8 and 11.

Because we are working with Integers in this example, instead of saying ≤8, we can just say > 7. However, if we needed to specify a score of ≥ 12, and ≥ 8 in the function, the function would read:

=IF((C3+D3)>=12,"Gold",IF((C3+D3)>=8,"Silver","No Prize"))

Example 2: The traditional application of nested IFs is in the classroom. When the instructor enters the students' scores into the worksheet, Excel displays the appropriate letter grade for each student.

This function construct can be copied and used in any application where grades or labels are awarded based on a numeric or alphabetical scale.

When working with nested IF statements, it is often useful to write the formula in Notepad or another simple editor in the following manner in order to keep track of the parentheses! You should be able to copy and paste the code below into a Notepad file. We have color-coded the pairs of parentheses.

=IF(B4>89,"A",
    IF(B4>79,"B",
      IF(B4>69,"C",
        IF(B4>59,"D","F"))))

In Conclusion

Let's end with a few important facts. First, all Excel functions start with an equal = sign unless they are imbedded inside other functions.

Second, consider locking cells containing functions and formulas, and protecting the worksheet, to prevent accidental erasure. Instructions for locking cells and protecting worksheets are provided in the tutorial Excel Made Easy: A Beginner's Guide.

Lastly, when creating complicated functions and formulas, it can be helpful to first type them into a Notepad file, and then copy and paste the function or formula into the cell. Notepad is a simple editor that comes free with Windows. Do not copy formulas or functions from Microsoft Word or other word processing programs.

We hope this article has been helpful. Cheers!

Return to the top of Excel Functions: IF and Nested IFs