How the IF Function Works
IF Function Overview
The IF function in Excel can be used to customize the content of specific cells depending on whether or not certain conditions in other worksheet cells that you specify are met.
The basic form or syntax of Excel's IF function is:
=IF(logic_test, value_if true, value_if_false)
What the function does is:
- Tests to see if a specified condition - the logic test - is true or false.
- If the condition is true, the function will carry out the action identified in the value if true argument
- If the condition is false, it will carry out the action specified in the value if false argument.
The actions carried out can include executing a formula, inserting a text statement, or leaving a designated target cell blank.
IF Function Step by Step Tutorial
This tutorial uses the following IF function to calculate an annual deduction amount for employees based on their yearly salary.
=IF(D6<30000,$D$3*D6,$D$4*D6)
Inside the round brackets, the three arguments carry out the following tasks:
- The logic test checks to see if an employee's salary is less than $30,000
- If less than $30,000, the value if true argument multiplies the salary by the deduction rate of 6%
- If not less than $30,000, the value if false argument multiplies the salary by the deduction rate of 8%
The following pages list the steps used to create and copy the IF function seen in the image above to calculate this deduction for multiple employees.
Tutorial Steps
- Entering the Tutorial Data
- Starting the IF Function
- Entering the Logical Test Argument
- Entering the Value if true Argument
- Entering the Value if false Argument and Completing the IF function
- Copying the IF Function using the fill handle
Entering the Tutorial Data
Enter the data into cells C1 to E5 of an Excel worksheet as seen in the image above.
The only data not entered at this point is the IF function itself located in cell E6.
For those who do not feel like typing, use these instructions for copying the data into an Excel worksheet.
Note: The instructions for copying the data do not include formatting steps for the worksheet.
This will not interfere with completing the tutorial. Your worksheet may look different than the example shown, but the IF function will give you the same results.
Starting the IF Function
The IF Function Dialog Box
Although it is possible to just type the IF function
=IF(D6<30000,$D$3*D6,$D$4*D6)
into cell E6 in the worksheet, many people find it easier to use the function's dialog box to enter the function and its arguments.
As shown in the image above, the dialog box makes it easy to enter the function's arguments one at a time without having to worry about including the commas that act as separators between the arguments.
In this tutorial, the same function is used several times, with the only difference being that some of the cell references are different depending on the location of the function.
The first step is to enter the function into one cell in such a way that it can be copied correctly to other cells in the worksheet.
Tutorial Steps
- Click on cell E6 to make it the active cell - this is where the IF function will be located
- Click on the Formulas tab of the ribbon
- Click on the Logical icon to open the function drop down list
- Click on IF in the list to bring up the IF function dialog box
The data that will be entered into the three blank rows in the dialog box will form the IF function's arguments.
Tutorial Shortcut Option
To continue with this tutorial, you can
- enter the arguments into the dialog box as shown in the image above and then jump to the last page that covers copying the IF function to rows 7 through 10;
- or follow through the next three pages which give detailed instructions and explanations for entering the three arguments.
Entering the Logical Test Argument
Entering the Logical Test Argument
The logical test can be any value or expression that gives you a true or false answer. The data that can be used in this argument are numbers, cell references, the results of formulas, or text data.
The logical test is always a comparison between two values, and Excel has six comparison operators that can be used to test whether the two values are equal or one value is less than or greater than the other.
In this tutorial the comparison is between the value in cell E6 and the threshold salary of $30,000.
Since the goal is to find out if E6 is less than $30,000, the Less Than operator " < " is used.
Tutorial Steps
- Click on the Logical_test line in the dialog box
- Click on cell D6 to add this cell reference to the Logical_test line.
- Type the less than key " < " on the keyboard.
- Type 30000 after the less than symbol.
- Note: Do not enter the dollar sign ( $ ) or a comma separator ( , ) with the above amount. An Invalid error message will appear at the end of the Logical_test line if either of these symbols are entered along with the data.
- The completed logical test should read: D6 < 3000
Entering the Value If True Argument
Entering the Value_if_true Argument
The Value_if_true argument tells the IF function what to do if the Logical Test is true.
The Value_if_true argument can be a formula, a block of text, a number, a cell reference, or the cell can be left blank.
In this tutorial, if the employee's annual salary located in cell D6 is less than $30,000 the IF function is to use a formula to multiply the salary by the deduction rate of 6%, located in cell D3.
Relative vs Absolute Cell References
Once completed, the intention is to copy the IF function in E6 to cells E7 through to E10 to find out the deduction rate for the the other employees listed.
Normally, when a function is copied to other cells, the cell references in the function change to reflect the function's new location.
These are called relative cell references and they normally make it easier to use the same function in multiple locations.
Occasional, however, having cell references change when a function is copied will result in errors.
To prevent such errors, the cell references can be made Absolute which stops them from changing when they are copied.
Absolute cell references are created by adding dollar signs around a regular cell reference, such as $D$3.
Adding the dollar signs is easily done by pressing the F4 key on the keyboard after the cell reference has been entered into a worksheet cell or into a function dialog box.
Absolute Cell References
For this tutorial, the two cell references that must remain the same for all instances of the IF function are D3 and D4 - the cells containing the deduction rates.
Therefore, for this step, when the cell reference D3 is entered into the Value_if_true line of the dialog box it will be as an absolute cell reference $D$3.
Tutorial Steps
- Click on the Value_if_true line in the dialog box.
- Click on cell D3 in the worksheet to add this cell reference to the Value_if_true line.
- Press the F4 key on the keyboard to make E3 an absolute cell reference ( $D$3 ).
- Press the asterisk ( * ) key on the keyboard. The asterisk is the multiplication symbol in Excel.
- Click on cell D6 to add this cell reference to the Value_if_true line.
- Note: D6 is not entered as an absolute cell reference as it needs to change when the function is copied
- The completed Value_if_true line should read: $D$3 * D6.
Entering the Value If False Argument
Entering the Value_if_false Argument
The Value_if_false argument tells the IF function what to do if the Logical Test is false.
The Value_if_false argument can be a formula, a block of text, a value, a cell reference, or the cell can be left blank.
In this tutorial, if the employee's annual salary located in cell D6 is not less than $30,000, the IF function is to use a formula to multiply the salary by the deduction rate of 8% - located in cell D4.
As in the preceding step, to prevent errors when copying the completed IF function, the deduction rate in D4 is entered as an absolute cell reference ($D$4 ).
Tutorial Steps
- Click on the Value_if_false line in the dialog box
- Click on cell D4 to add this cell reference to the Value_if_false line
- Press the F4 key on the keyboard to make D4 an absolute cell reference ( $D$4 ).
- Press the asterisk ( * ) key on the keyboard. The asterisk is the multiplication symbol in Excel.
- Click on cell D6 to add this cell reference to the Value_if_false line.
- Note: D6 is not entered as an absolute cell reference as it needs to change when the function is copied
- The completed Value_if_false line should read: $D$4 * D6.
- Click OK to close the dialog box and enter the completed IF function into cell E6.
- The value of $3,678.96 should appear in cell E6.
- Since B. Smith earns more than $30,000 per year, the IF function uses the formula $45,987 * 8% to calculate his annual deduction.
- When you click on cell E6, the complete function
= IF ( D6<3000,$D$3*D6,$D$4*D6) appears in the formula bar above the worksheet
If steps in this tutorial have been followed, your worksheet should contain the same IF function seen in the image on page 1.
Copying the IF Function using the Fill Handle
Copying the IF function using the fill handle
To complete the worksheet, we need to add the IF function to cells E7 to E10.
Since our data is laid out in a regular pattern, we can copy the IF function in cell E6 to the other four cells.
As the function is copied, Excel will update the relative cell references to reflect the function's new location while keeping the absolute cell reference the same.
To copy down our function we will use the Fill Handle.
Tutorial Steps
- Click on cell E6 to make it the active cell.
- Place the mouse pointer over the black square in the bottom right corner. The pointer will change to a plus sign " + ".
- Click the left mouse button and drag the fill handle down to cell F10.
- Release the mouse button. Cells E7 to E10 will be filled with the results of the IF function.