Excel 2016: Making Decisions with the IF Function

Updated 2017.07.30

Objectives:

·        Use the if function to make a decision about the value to place in a cell.

The IF Function

Use this spreadsheet for the examples on this page.

IF function overview

The IF function allows you to choose one of two values to put into a cell. For example, a teacher might want to create a spreadsheet that puts the letter “P” in a cell if a student passes a class, and puts the letter “F” in a cell if a student fails the class. If the threshold for passing the class is 70, you might specify the rule for determining the student’s grade like this: If the student’s score is 70 or more, assign the letter “P”, otherwise assign the letter “F”. This rule has three parts:

·        The condition for passing: score is 70 or more

·        The value to put in the cell if the condition is TRUE: "P"

·        The value to put in the cell if the condition is FALSE: "F"

If function arguments

An IF function must always be supplied with three arguments:

(1) The condition that is to be examined. Excel calls this the logical test. This is always something that is either true or false. In this case the condition is score is 70 or more. In Excel, this is written as score >= 70, where "score" is the cell with the student's score in it. Sometimes instead of calling the condition a logical test, some people call it a Boolean expression.

(2) The value that is to be placed in the cell if the condition is true. In this case, the value that is to be placed in the cell is “P”.

(3) The value that is to be placed in the cell if the condition is false. In this case, the value that is to be placed in the cell is “F”.

Logical Tests

Logical tests are almost always created by comparing two values using one of Excel’s six relational operators. Excel uses the following relational operators:

Is equal to

=

Is greater than

Is less than

Is not equal to

<> 

Is greater than or equal to

>=

Is less than or equal to

<=

 

Note that the order of the characters makes a difference. You cannot use >< for not equal; you cannot use => for greater than or equal to; you cannot use =< for less than or equal to.

Example

Go to the Gradebook IF worksheet in the sample spreadsheet.

http://old.briarcliff.edu/departments/cis/Excel/2013/014-IF-Function_files/image001.jpg

We need to put a formula in C2 that will automatically calculate the student’s grade by comparing it to the value in cell E2. Note that the reference to E2 (70) is an absolute cell reference. This will allow us to copy the formula to rows 3 and 4 correctly.

We need to use an IF function to determine if the score is >= 70, so:

·        Click on the Formulas tab

·        In the Function Library group, click on the Logical book.

·        From the list, click on IF.

·        The following Function Arguments dialog box will appear.

·        For Logical Test, enter B2>=$E$2.

·        For Value if true, enter P (Excel will provide the quotation marks around it when you leave the cell).

·        For Value if false, enter F (again, Excel will provide the quotation marks around it when you leave the cell).

·        Click on the OK button.

http://old.briarcliff.edu/departments/cis/Excel/2013/014-IF-Function_files/image002.jpg

·       The function that gets entered into the cell is: if(B2>=$E$2,"P","F"):

http://old.briarcliff.edu/departments/cis/Excel/2013/014-IF-Function_files/image003.jpg

When we press the Enter key and Excel evaluates the IF function, it determines that B2 (80) is greater than or equal to E2 (70), so it displays the second argument (the letter “P”) in the cell.

http://old.briarcliff.edu/departments/cis/Excel/2013/014-IF-Function_files/image004.jpg

Use the fill handle to copy the formula to rows 3 and 4:

http://old.briarcliff.edu/departments/cis/Excel/2013/014-IF-Function_files/image005.jpg

Your formulas should be:

http://old.briarcliff.edu/departments/cis/Excel/2013/014-IF-Function_files/image006.jpg

And Excel will correctly use the IF function to determine the correct grade for the cells C3 and C4.

http://old.briarcliff.edu/departments/cis/Excel/2013/014-IF-Function_files/image007.jpg