3.2 Logical and Lookup Functions

Learning Objectives

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, 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:

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. Make sure that Q5 is your active cell.
2. On the Formulas tab, in the Function Library group, find the IF function on the Logical pulldown menu (see Figure 3.9).
    Mac Users: There is no “Function Library” group for Excel for Mac. Mac Users should click on the Formulas tab, then click the “Logical” tool    list arrow, and choose IF (see Figure 3.9).

Now you will see the IF Function dialog box, with a place to enter each of the three arguments.
 Mac Users: There is no “dialog box”. The “Formula Builder” pane will display at the right side of the Excel window. It has the same layout as Figure 3.10 below.

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.
    Mac Users should click the “Done” button, then close the Formula Builder pane.
   (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”) (see Figure 3.11).

VLOOKUP Function

You need to use a VLOOKUP function to look up information in a table. Sometimes that table is on a different sheet in your workbook.  Sometimes it is in another file entirely.  In this case, we need to know what grade each student is getting based on their percentage score. You will find the table that defines the scores and the grades in A28:B32.

There are four pieces of information that you will need in order to build the VLOOKUP syntax. These are the four arguments of a VLOOKUP function:

Let’s create the VLOOKUP to display the correct Letter Grade in column R.

 Mac Users will use the “Formula Builder” pane at the right side of the Excel Window.

1. • Lookup_value – In this case, we will use the Percentage score. So, P5 for the first lookup value.
   • Table_array – This is the range that contains the value you want returned by the function. In this case, that range is A28:B32. Note that this range does NOT include the label in row 27; just the actual data.  The cell references for the Table_array need to be absolute – $A$28:$B$32. When we copy this function to the other cells, we do not want these cell references to change. It should always be $A$28:$B$32. This is very important! They must have the absolute reference symbols or the calculations will not work.
   • Col_index_number – This is the column in the table array range that includes the information that we are looking up. In our case, the actual grades are in the 2nd column of the range. So, the column index will be 2.
   • Range_lookup – In some cases, you will need something in the Range_lookup box. Since we are looking for an approximate match for the percentages, we want the default value of TRUE, so we do not need to enter anything for this argument.
2. While you are in the dialog box, be sure to look at all the helpful definitions that Excel offers.
3. When you have filled in the dialog box, press OK.
    Mac Users should click the “Done” button, then close the Formula Builder pane.
4. The calculation you will see in the formula bar is: =VLOOKUP(P5,$A$28:$B$32,2)
5. Use the fill handle to copy the function down through row 24. The results displayed should match Figure 3.14.

Note: What if it didn’t work? What if you get a result different from the one predicted? In this case, either you have made a previous error, resulting in different % scores than this exercise anticipated, or you made a mistake entering your VLOOKUP function.

To make repairs in the function, make sure that R5 is your active cell. On the Formula bar, press the Insert Function button (see Figure 3.15). That will reopen the dialog box so you can make your repairs. Did you forget to make the cell references for the Table_array absolute? Did you use the wrong cell for the Lookup_value? Press OK when you are done and recopy the corrected function.

Error Messages

Sometimes Excel notices that you have made errors in your calculations before you do. In those cases Excel alerts you with some slightly mysterious error messages. A list of common error messages can be found in Table 3.1 below.

Table 3.1 – Common Error Messages

This table was copied from the internet. Look here for additional information.
http://www.dummies.com/software/microsoft-office/excel/how-to-detect-and-correct-formula-errors-in-excel-2016/

Date Functions

Very often dates and times are an important part of Excel data. Numbers that are correct today may not be accurate tomorrow. So, it is frequently useful to include dates and times on your spreadsheets.

These dates and times fall into two categories – ones that:

Take a look at the list of Date and Time functions offered in the Function Library on the Formulas tab (see Figure 3.16).

For our gradebook, we want the date and time to be displayed in A2, and it needs to update whenever the workbook file is opened.

Excel will update this field independently whenever you save and re-open the file, or print it. It may happen more frequently than that – depending on how you have set this up in your installation of Excel.

Another variation of the current date is the TODAY function. Let’s try that one next.

1. Make sure A2 is your active cell. Press Delete to remove the NOW function.
2. From the Date & Time drop-down list in the Function Library on the Formulas tab (see Figure 3.16), select TODAY and then click OK.
    Mac Users click the “Done” button in the “Formula Builder” pane; then close the pane.
3. The result you will see in the formula bar is: =TODAY(). The result you will see in A2 depends on the current date. Since we haven’t asked for the time, the time you are seeing is likely 12:00. That is not very helpful so we need to change the format of the date.
4. On the Home tab, in the Number group, press the Number Format Launcher button (see Figure 3.17).
5. In the Format Cells dialog box, click the Number tab. Choose the Date category and select Wednesday, March 14, 2012 (this format is called Long Date).
    Mac Users: there is no Number Format Launcher button or “Format Cells” dialog box.
   Click the list arrow next to “Date” and choose “Long Date”  
6. The current day and date will display in A2.

Keyboard Shortcuts

Sometimes you want the date or the time to show up in your spreadsheet, but you don’t want it to change. You can simply type in the date or time. Or, you can use shortcut keys.

Key Takeaways