Fabulous Examples of Excel Functions:
The IF Function
This tutorial covers the IF function of Excel and provides helpful worksheet examples. We also discuss the common practice of using the AND function inside of the IF function. (See our separate AND Function tutorial.)
Often our data analysis requires that we use multiple IF functions, or nested IFs, in one expression. Our tutorial, Nested IF Functions, provides examples of solving more complex logic problems with nested IFs.
Prefer video learning? We recommend Excel 2010 Tutorial for Beginners. We have this course (over 9 hours) and think it is a very good value. Some chapters are available online for free, and an advanced course is also available.
The IF Function
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:
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 The SUM 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.
The IF Function with the AND Function
Here is an example of an IF function that contains an AND function as the logical test. In summary, the AND function asks Excel to test multiple conditions. If all the conditions are met, Excel returns a value of TRUE. Otherwise, Excel returns a value of FALSE. (For more information about the AND function, see The AND Function tutorial.)
The IF function allows us to tell Excel explicitly what to display in the cell depending on the result of the AND function.
Students pass a course based on two final exam scores. The first score, stored in cell D5, must exceed 19, and the second test score, in cell D6, must exceed 69. The function below reads: If the value in D5 is greater than 19 AND the value in cell D6 is greater than 69, please display the value-if-true, which is "Pass." Otherwise, display "Fail."
When creating complicated functions and formulas, consider typing them first in a Notepad file with a large font, and then copy and paste the expression in the spreadsheet cell. Notepad is a simple editor that comes free with Windows. Do not use Microsoft Word or other word processing programs that add formatting.
We hope this article has been helpful. Cheers!
Please link to this tutorial using the following HTML:
<a href="http://www.keynotesupport.com/excel-functions/if-function.shtml">Fabulous Examples of Excel Functions: The IF Function</a>
- » AND
- » AND with IF
- » AVERAGE
- » COUNT
- » COUNTA
- » COUNTBLANK
- » COUNTIF - Basics
- » COUNTIF - Advanced
- » Date and Time: How Excel Handles
- » DATEVALUE
- » EVEN and ODD
- » IF
- » IF (Nested)
- » MAX
- » MEDIAN
- » MIN
- » MODE
- » MROUND
- » Nested IF Functions
- » NETWORKDAYS
- » NOW
- » ODD and EVEN
- » OR
- » OR with IF
- » POWER
- » PRODUCT (Multiplying)
- » ROUND
- » ROUNDDOWN and ROUNDUP
- » SQRT
- » SUM
- » SUMPRODUCT
- » SUMSQ
- » TODAY
- » TRUNC
Search Keynote Support