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:

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

• The value you want to lookup, also called the Lookup_value. In our example, the lookup value will be the student’s percentage score in column P.
• The Table_array is the range (table) where the lookup values and the values you want returned by the function are located. In our example, this is the table of percentages and corresponding letter grades in the range A28:B32. The lookup value should always be in the first column in the table array for VLOOKUP to work correctly. For example, in our table_array the lookup value is in cell A28, so the range should start with A.
• The Col_index_num is the column number in the range that contains the value to return. In our example, when you specify A28:B32 as the Table_array range, you should count A as the first column (1), B as the second column (2), and so on. You will enter the appropriate column number in this box as 1, 2, or 3 and so on.
• In the Range_lookup, you can optionally specify TRUE if you want an approximate match or FALSE if you want an exact match of the return value. If you leave this blank, the default value will always be TRUE, or approximate match.

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

1. Make sure that R5 is your active cell.
2. On the Formulas tab, in the Function Library, find the VLOOKUP function on the Lookup & Reference pull-down menu (see Figure 3.12).
    Mac Users should click the Lookup and Reference tool list arrow  to find the VLOOKUP function.

   

3. Fill in the dialog box so that it looks like the image in Figure 3.13.

 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

In Chapter 2 we discussed the various error messages that occur when working with formulas and functions. If you need a refresher of these error message, take a look through the error message cards again.

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:

• Remain the same. For instance, if a spreadsheet includes data for May 15th, you don’t want the date to change each time you re-open the spreadsheet.
• Change to reflect the current date/time. When it is important to have the current date or time on a spreadsheet, you want Excel to update the information regularly.

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.

1. Make A2 your active cell. Notice that A2 extends all the way from column A to Column R. Previously, someone has used the Merge & Center tool on this cell to make it match the title above.
2. On the Formulas tab, in the Function Library, select NOW from the Date & Time drop-down list and then click OK.
    Mac Users click the “Done” button in the “Formula Builder” pane at the right side of the Excel window; then close the pane.
3. The result you will see in the formula bar is: =NOW(). The result you will see in A2 depends on the current date and time. The NOW function is a very handy function. It takes no arguments and is Volatile! That is not as alarming as it may seem. This just means that you don’t need to give it any more information to do its job and that your results will change frequently. You can update the date and time whenever you want – you don’t have to wait until you open the workbook again.
4. Make sure that A1 is your active cell and press the F9 function key (along the top of your keyboard.) The time will update.

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.

 

Attribution

Adapted from Beginning Excel 2019 and licensed under CC BY.