In addition to doing arithmetic, Excel can do other kinds of functions based on the data in your spreadsheet. In this section we will use an =IF function to determine whether a student is passing or failing the class. Then, in the next section, we will use a =VLOOKUP function to determine what grade each student has earned.

IF Function

The IF function is one of the most popular functions in Excel. It allows you to make logical comparisons between a value and what you expect. In its simplest form, the IF function says something like:
If the value in a cell is what you expect (true) – do this. If not – do that.

The IF function has three arguments:

• Logical test – Here, we can test to see if the value in a selected cell is what we expect. You could use something like “B7=14” or “B7>12” or “B7<6”
• Value_if_true – If the requirements in the logical test are met – if B7 is equal to 14 – then it is said to be true. For this argument you can type text – “True”, or “On budget!” Or you could insert a calculation, like B7*2 (If B7 does equal 14, multiply it by 2). Or, if you want Excel to put nothing at all in the cell, type “” (two quotes).
• Value_if_false – If the requirements in the logical test are not met – if B7 does not equal 14 – then it is said to be false. You can enter the same instructions here as you did above. Let’s say that you type the double quotes here. Then, if B7 does not equal 14, nothing will be displayed in this cell.

In column Q we would like Excel to tell us whether a student is passing – or failing the class. If the student scores 70% or better, he/she will pass the class. But, if he/she scores less than 70%, he/she is failing.

1. 1. Make sure that Q5 is your active cell.
   2. On the Formulas tab, in the Function Library, find the IF function on the Logical pulldown menu (see Figure 3.9).
      

      

Now you will see the IF Function dialog box, with a place to enter each of the three arguments.

1. Click in the box for Logical Test. We need to test whether a student’s score is less than .7. So, in this box, type P5<.7
2. Click in the box for Value_if_true. If the student’s score is less than .7, then they are failing the class. In this box, type Fail.
3. Click in the box for Value_if_false. If the student’s score is NOT less than .7, then they are passing the class. In this box, type Pass.
4. Make sure that your dialog box matches Figure 3.10.
   

   

   While we are here, let’s take a look at the dialog box. Notice that as you click in each box, Excel gives you a brief explanation of the contents (in the middle below the boxes.) In the lower left hand corner, you can see the results of the calculation. In this case DeShae is passing the class. Below that is a link to Help on this function. Selecting this link will take you to the Excel help for this function – with detailed information on how it works.

5. Once you have typed in the required arguments and reviewed to make sure they are correct, press OK.  (The text Pass should be displayed in Q5 because DeShae is passing the class.)
6. Use the Fill handle to copy the IF function down through row 24.
7. Click on Q5. When you look in the formula bar, you will see the IF calculation: =IF(P5<0.7,”Fail”,”Pass”).
8. Observe the pattern of the formula across your rows. How many students students have passed or failed?

Attribution

3.2 Logical and Lookup Functions by Noreen Brown and Mary Schatz, PCC.EDU, is licensed under CC BY 4.0