Chapter 5 - Spreadsheet Functions

Chapter 5 Spreadsheet Functions

Chapter Outline

Spreadsheet Functions

PMT Function

VLOOKUP Function

IF Function

Nested IF Statement

Combining Functions Together

Cell Protection

Gridlines

Naming and Deleting Sheets

Range Names

Referring to cells in a different sheet

What’s wrong with this function

Review of Menus

Spreadsheet Functions

There are several hundred functions available to you in a spreadsheet. The easiest way to learn all of them is to choose Help, Search ... and search for Functions. This should give you an alphabetical listing. All functions have a function name followed by the parameters (arguments) of the function. Some parameters are simple like a cell range, others are more complex like a logical condition, action for true condition and action for false condition. Functions are really built in formulas. In other words for each function there is a mathematical formula that will do the same thing. However the formula is usually long and complicated so the makers of the spreadsheet have provided you with functions to simplify the process.

The easiest way to do any function is to write the function and all the parameters out by hand first. I will describe how to do this with the PMT function.

PMT Function

The PMT or Payment function can figure out what your monthly payment on a loan will be, given the interest rate, the number of payments and the principal amount. Let’s look at the arguments for this function and talk about them for a moment.

=PMT(rate, nper, pv, fv, type)

rate is the interest rate for your loan.

nper is the number of payments you will be making.

pv is the present value or the principal amount of your loan. When you want a monthly payment you need to make this a negative amount to make your payment a positive number.

fv and type are not required parameters. We will not use them for now.

As always in order to get a complete description of the PMT function simply use help and search for it. One important thing to note is that I have already read the help so I know that if I want to have monthly payments I need to make sure the rate and nper are both in months. The interest on most loans is quoted as an annual interest rate. Since nper is the number of monthly payments that you want to make, you need to adjust the annual interest rate to a monthly one by simply dividing the rate by 12.

Following the basics of spreadsheets I want to use cell references for my functions whenever possible. I also want to write it out on paper first since it will make it easier. Figure 1 is how I want my spreadsheet to look:

Remember the four things a computer can do input, processing, output and storage. In our case here you can see the input numbers, the function itself is the processing and the output is our monthly payment displayed on the screen. I have used a simple formula to calculate the total payments by taking the number of years and multiplying by 12. Or in spreadsheet language =B3*12 is the formula located in cell B4. Now let’s figure out the PMT function. The first step is to put it all on paper like this:

=PMT(rate, nper, pv, fv, type)

rate - is our annual interest rate or .0799 (7.99%) remember that when you take the % sign off you need to move the decimal point 2 places to the left. We also need to divide this by 12 so to get the monthly interest rate, .0799/12.

nper - is the number of payments that we want to make. We want a 4-year loan so that would be 48 payments.

pv - is the principal amount of our loan or 15000. Again you want this to be a negative number so it should be -15000. The payment function will return a negative number so if we make the principal negative the function will return a positive number. We do not want to use commas when writing this number since the computer would think that we are separating parameters.

Now we could plug in the actual numbers to come up with the function like this:

=PMT(.0799/12,48,-15000)

Now this would work but it is not very flexible. If we wanted to change the amount, the term, or the interest we would have to edit the function. When you use cell references you can simply type the number in the correct cell.

Once you figure out the function in ‘English’ like we did above, you need to then convert your English into spreadsheet. So let’s do that now:

=PMT(rate, nper, pv, fv, type)

rate -.0799/12 - We find the interest rate in cell B2 so let’s write the rate as B2/12

nper - 48 payments can be found in cell B4.

pv - 15000 - We can find the principal in cell B1 and we need to make it a negative number so -B1

=PMT(B2/12,B4,-B1)

Now we can simply change the amount we borrow, the interest rate or the number of years for our loan in the correct cell and automatically come up with our new monthly payment. So now you can put in the amount of that new car you want and get a rough idea of how much it will cost you. Can you develop the formula to tell you how much you will actually have to pay the bank[1]? How much in interest will you pay on this loan[2]?

Developing your functions (or formulas) in this step by step approach helps you develop the logical thinking required for more complex problems. So in addition to learning functions you are also using a problem solving step by step approach.

VLOOKUP Function

The VLOOKUP or Vertical Table Lookup function will search a table for a specified number and return a number for you. An example would be a sales tax table like Figure 2. If you make $1 purchase in Vermont you will have to pay a 5¢ sales tax. Looking at the sales tax table we search through the first column until we find our purchase amount of $1. Then we go over to column two and find our tax of 5¢. Let’s look at the parameters for the VLOOKUP function and see how they would fit our simple sales tax table:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Lookup_value is the value that you are going to look up in your table. In the sales tax example it will be our purchase amount or $1. The computer will always look for the value in the first column of your table only.

Table_array is the cell range that your table is in. This would start from the first column, first row ($0.00) and go to the last column, last row (0.05). You do not include the labels, only numbers. Naturally you would need to use cell references.

col_index_num is the column number that contains the information that you are looking for. In the sales tax table we are looking for the sales tax that is located in column two, so our column index number is 2. Column A, Column B is not what is meant by the Column Index Number. Instead the first column in your table range is column 1, the second column in the range is column 2.

range_lookup is an optional parameter that is used if your first column is not in ascending sorted order (1,2,3). When not specified it is set to TRUE. If for some reason your first column of your table is not in sorted order you can set this parameter to FALSE. In the case of TRUE, VLOOKUP will return a value that is equal to or the next largest value that is less than the lookup_value. In the case of FALSE the lookup_value must match exactly.

If VLOOKUP can't find the lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value. In other words it will always take your lookup value and round it down. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value. So if your lookup value is too small (in the example less than 0) the computer will let you know, however if the value is too large (greater than 1.09) it will just give you the last number in the table. For example if your purchase amount is $10, the VLOOKUP will return a 0.05 sales tax! That is just the way it works.

If VLOOKUP can't find lookup_value and range_lookup is FALSE, VLOOKUP returns the #N/A value. This is because the FALSE value says the lookup_value must match the table exactly.

Looking at Figure 3 we would place the VLOOKUP function in cell B2, to find the tax on our purchase amount in cell B1. AS the arrow points the VLOOKUP returns the 0.03 sales tax.

Thus writing our VLOOKUP function out on paper it would look like this:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

lookup_value is our purchase price

table_array is the cell range our table is located in

D2:E12

col_index_num will be 2 since we want to display our sales tax.

Now putting all this together our VLOOKUP function would look like this:

VLOOKUP(B1,D2:E12,2)

Let’s look at a couple of samples:

Our Purchase Amount VLOOKUP will return

< 0 NA

> 1 0.05

0.40 0.02

0.85 0.04

1.00 0.05

1.10 0.05

50.00 0.05

What would the sample spreadsheet display if we changed the column_index_num to 1? Currently it is set to 2 and with a purchase amount of $ 0.50, our sales tax is 0.03. If we change the column_index_num number to 1 it would simply display 0.50 which is in the first column.

IF Function

The IF function is used when you want to display one of two possible answers. In other words an IF function is a question that has a yes or no answer. You then can display one number if the question is answered yes, or a different number if the answer is no. Let’s look at the parameters of the IF function:

IF(logical_test, value_if_true, value_if_false)

logical_test - is the yes/no question. An example would be “Is hours worked greater than 40”? This is a question that will have a yes or no answer. In spreadsheet terms it would look like C3>40 (providing C3 contains the hours worked).

value_if_true - is the action that you are going to take if the answer is yes (true). Now the action can be a number, a label, another function or even a formula. Some examples would be:

display some text - “Overtime!”

display a cell contents - B3.

Calculate regular pay as 40 times pay rate - 40*B3 (assuming B3 holds the pay rate).

Calculate overtime pay as (hours worked - 40) times pay rate times time and a half - (C3-40)*B3*1.5. Notice the use of parentheses in this case to ensure that I perform the subtraction first.

value_if_false - is the action that you are going to take if the answer is no (false). The action can be the same as any examples that I used for value if true. Sometimes you may want to do nothing. An example of a do nothing action would be:

display zero - 0.

display nothing - “”

Let’s figure out a couple of IF statements. An easy one, is to use a spreadsheet to calculate regular pay, overtime pay and total pay. In order to do this we will need to have for input, the hours worked and the pay rate. We will assume an overtime rate of time and a half. Let’s look at the sample spreadsheet in Figure 4 and prepare our functions by hand. One thing that you may want to do is to use easy numbers. For example I may pay people (like Angie) $20 per hour (they wish) but if I use a $1 hour pay rate like I pay Adam it will be very easy for me to calculate the answers. I know regular pay will be $40 and overtime pay will be $1.50. The multiplication is just easier.

Let’s start with the formulas for Adam and figure Regular Pay for cell D3. You need to use the if statement:

IF(logical_test, value_if_true, value_if_false)

logical_test our logical test is going to be did they work over 40 hours or is hours worked greater than 40? In a spreadsheet it would read
C3>40

value_if_true would be yes they did, so regular pay is going to be for 40 hours or 40 times their pay rate.
40*B3

value_if_false would mean they did not work more than 40 hours therefore regular pay would be the hours they worked times their pay rate
C3*B3

Put all that together in the IF statement for cell D3 (Regular Pay) will look like this:

=IF(C3>40,40*B3,C3*B3)

Looking at the IF statement is confusing to me but if we work out the function in English first, it really is not that hard. Let’s try Overtime Pay as it can be a little harder. I am going to change the wording of the parameters and give the commas a word. Maybe the IF statement will make more sense to you this way in Figure 5. The parameters are still the same only worded differently. You may want to substitute this wording if you find it easier.

The short cut that I use to write this statement is =if(?,Y,N). Now let’s work on the formula for overtime pay in cell D3.

Question (logical_test) our logical test can be the same, did they work over 40 hours or is hours worked greater than 40?
C3>40

Yes its true (value_if_true) would be yes they did, so we need to pay them for the hours they worked over 40 times time and a half. An easy way to do this is to figure the hours times the pay rate times 1.5.
(C3-40)*B3*1.5

No it’s false (value_if_false) would mean they did not work overtime and should not get any overtime pay so it should be 0.
0

Put all that together in the IF statement for Overtime pay in cell E3 will look like this:

=IF(C3>40,(C3-40)*B3*1.5,0)

=IF( ? , Y ,N)

Again it is easier to write the statement out in ‘English’ first and then convert it to spreadsheet IF function. The gross pay is simply a sum function adding regular pay and overtime pay.

Let’s do all the same formula for Angie’s Regular pay in cell D4:

IF(logical_test, value_if_true, value_if_false)

logical_test is hours worked greater than 40
C4>40

value_if_true 40 times their pay rate.
40*B4

value_if_false hours they worked times their pay rate
C4*B4

Put all that together in the IF statement for regular pay in cell D4 will look like this:

=IF(C4>40,40*B4,C4*B4)

Not much difference is there? Only the row numbers change. Does that sound like we can copy the formula for all the employees? Yes we can, and since we are using relative cell address they will automatically change. In fact all the formulas in Figure 4 can be copied down to the next row. There is an easy way to do this and that is to highlight from D3 to F9. We can use fill down (Chapter 6 Edit Fill Down). A very handy feature. Since we used relative cell references they automatically will change.

Nested If Statement

Let me give you an example of a nested if statement (2 ifs in one formulas). As always I want to start simple. Looking at Figure 6 cell B2 holds a students total points. What I need is a formula to display the students final letter grade. So, let's make this simple, in my class I give 2 grades, A or F (hey that sounds like a good idea!). If your total score is greater than 89 you will get an A else you will get an F. I start by writing out the parameters of the IF statement. I am going to use my little shortcuts this time:

=IF(?,Y,N)

my question is going to be:

? is total score > 89

in spreadsheet format this will be:

B2>89

If the answer to that question is yes then:

Y "A"

And if the answer to that question is no then:

N "F"

Putting that all in one statement:

=IF(B2>89,"A","F")

Okay so maybe that would make it easier for me to figure out the final grades but I think that maybe we should at least give people a "B" if they get a score greater than 79. So what we have now is:

If your grade is greater than 89 you will get an A else

If your grade is greater than 79 you will get a B else

You will get an F.

You should notice that the first part of the formula will stay the same. What we need to do is change the "F" (I hear that one all the time!). So on paper we have something like this:

IF( ? , Y , N )

? Score > 89

B2 > 89

Y "A"

N IF( ? , Y , N )

? Score > 79

B2 > 79

Y "B"

N "F"

Putting that all into one statement we get:

=IF(B2>89, "A", IF(B2>79, "B", "F"))

Make sense to you? I also suggest that you type the formula in one step at a time so that you can be sure each step is working. Looking at our formula I suppose we really need to add the letter grades C and D. Eventually we would get the complete formula like this:

=IF(B2>89, "A", IF(B2>79, "B", IF(B2>69, "C", IF(B2>59, "D", "F"))))

Cool. I wonder if we should do the + and - grades as well? Go for it, it just makes one very long nested IF formula! I know because I have typed it in to help me calculate the final grade for everyone.

Combining Functions Together

You can have an IF statement inside of another IF statement as the Value_if_true. This would be called a nested IF. You can also combine VLOOKUP and IF together. When you combine functions it is really not any harder than doing them one at a time. Remember to write the whole thing out on paper first and you will be able to understand it easier.

Let’s try to combine an IF and VLOOKUP together to see how this would work. Remember that if the VLOOKUP statement tried to look up a number that was too large for our table it would just use the last number. Looking at our tax table in Figure 7 we see if our purchase price was $10.00, we would need to use a different table, however the VLOOKUP function would return 0.05 as our tax. Now we can use the If statement to check to see if the lookup value is not too large for our table. Let’s do this in ‘English’ first. We will start with the IF statement

IF(logical_test, value_if_true, value_if_false)

logical_test is the purchase price less than or equal to the last number in our table
B1<=D12

value_if_true now if this is true we need to do our VLOOKUP statement: VLOOKUP(lookup_value, table_array, col_index_num)

lookup_value is our purchase price

table_array is the cell range our table is located in

D2:E12

col_index_num will be 2 since we want to display our sales tax.

Now putting all this together our VLOOKUP function would look like this:

VLOOKUP(B1,D2:E12,2)

value_if_false we do not want to look it up at all. However we may want to display a message. There is a function called NA for Not Applicable that we can use. It is simply NA(). So we can put that in the value_if_false:
NA()

Put all that together in the IF statement it will look like this (be sure to match the parenthesis):

=IF(B1<=D12,VLOOKUP(B1,D2:E12,2),NA())

Now when we type this statement in to cell B2 our spreadsheet will look like Figure 8. This way the user can see that something is wrong and they should look at the input numbers to see why. The NA causes the SUM function to return NA as well.

One quick note is that there can be several ways in which you can do an IF function and they are all correct. For example when using an IF statement you can have your condition C3>40 or C3<40. The difference is that your actions would be switched around.

Cell Protection

You may want to use cell protection to keep people (including yourself) from deleting or typing over a function, formula, number or label. This is a way to customize your spreadsheet so that people can only enter into the spreadsheet the input numbers and not change any of your formulas.

Looking at the Sales tax spreadsheet Figure 8 we would not want someone to simply type over “NA”, after all that was a hard formula to develop. In fact the only place that we would want someone to type any information into would be the purchase price located in cell B1. We can accomplish this by protecting our worksheet

Cell protection is a two step process:

Step one: Format the input cells (in our example cell B1) to unlocked. This is done through Format, Cells, Protection. Be sure the check box for Locked is turned off. In Figure 9 the locked feature is currently on.

Step two: Is to turn the protection feature on. This is done by choosing Tools, Protection, Protect Sheet.... Until you turn this feature on, the locked/unlocked cells do not mean a thing. You can give this protection a password so that no one can turn the feature off unless they know the password.

You should turn the sheet protection on only after you have finished your spreadsheet. If you need to make a change to something you will need to turn the sheet protection off, make your change and then turn the protection feature back on. To turn protection off choose Tools, Protection, Unprotect Sheet.... Then be sure to turn the protection feature back on when you are ready.

When someone tries to change a cell that is locked, a message box appears and says that you can not change a locked cell (Figure 10). You will also notice that a lot of menu options in the pull down menus have become grayed out, meaning they are not available because you have protected the document. In Figure 11 the format pull down menu notice that Cells… is grayed out.

A really neat feature of protecting your spreadsheet is that you can use the tab key to move from one unprotected cell to the next unprotected cell. Inform the person using the spreadsheet of this by telling them to hit the tab key after they type in the information into a cell. The cursor will then automatically go to the next input cell. One thing to keep in mind is that this tab feature will move across the columns and then go down to the next row. In other words if you have cells A1:B2 unlocked and you have protected your document, when you press the tab key you will move from A1 to B1. Then you will go to A2 then B2. Then you will go back to A1. You may want to think about this as you are designing your spreadsheet layout. In Figure 9 the cursor will not move from cell B1 since we have only one unlocked cell.

Gridlines

Once you start formatting your spreadsheet with borders, you may decide that you do not want to look at the gridlines or print them anymore. If you do not want to print the gridlines you would turn the check box off that is found under File, Page Setup, Sheet, Gridlines.

To have the gridlines no longer displayed on the screen can be found under Tools, Options..., View, Gridlines check box (about in the middle of figure 12).

In this case the check box is turned on so the gridlines will be showing. Simply turn this off if you do not want to display the gridlines. There are a ‘ton’ of options available to you in this dialog box however most of them all go beyond the scope of the course. Just remember that you can always choose help (or look in the manuals that came with the software) to learn more about these features.

Naming and Deleting Sheets

It is real easy to name sheets. The sheet names are all at the bottom and you can move between them by clicking on the name or using the sheet navigation buttons shown in Figure 13. You should name your sheets with an appropriate or relevant name. Simply have the sheet active and choose Format, Sheet, Rename and type in the name that you wish to use. You can also double click on the sheet name and then type over it.

You should also delete the extra sheets that you are not using. Simply click on the sheet tab (to delete more than one sheet hold the shift key down as you click on the sheet names) and choose Edit, Delete Sheet.

You can also copy a sheet that you have completed. Simply Choose Edit, Move or Copy Sheet... to get the dialog box shown in Figure 14. Click on the sheet that you want to copy (this is where having descriptive names comes in handy) and click on the Create a Copy check box. This will create a copy of the sheet that you have specified including all your formatting features.

Referring to cells in a different sheet

You can refer to cells in another sheet by using the range name. Just start typing the formula or function as you would normally. When you get to the part that you want a range name just type that name in or choose the list box on the formula bar as shown in Figure 21. This will present you with a list of range names that are available in this workbook. Just choose the correct name. If you are trying to refer to cells that are not named just use your mouse to click on the sheet and then highlight the cell range that you want.

When you refer to a cell in another sheet you will notice that Excel will put the sheet’s name as well as the cell name in the formula. This is called the syntax. If you wanted to type the cell reference you can as long as you include the correct syntax in this form: ’sheetname’!cellreference. Where the sheetname is simply the name of the sheet, cell reference is the cell or range of cells and they are both separated by the ! with no spaces.

To continue typing in the function just type the comma, parenthesis or math operator and this will bring you back to the sheet where you have the formula that you are working on.

What’s wrong with this function

PMT(0.899/12,48,15,000)

Two things are wrong with this function. First there are no cell references! Always use a cell reference when possible. The second thing wrong is the comma in 15,000. The computer will think that the comma is separating parameters and that your principal is 15 instead of 15,000!

Here is a list of error messages that may show up in a cell and their meanings as taken from the Microsoft Excel Help file.

Error value Meaning

#DIV/0! The formula is trying to divide by zero.

#N/A No value is available. Usually, you enter this value directly into worksheet cells that will eventually contain data that is not yet available. Formulas referring to those cells will return #N/A instead of calculating a value.

#NAME? Microsoft Excel does not recognize a name used in the formula.

#NULL! You specified an intersection of two areas that do not intersect.

#NUM! There is a problem with a number.

#REF! The formula refers to a cell that is not valid.

#VALUE! An argument or operand is of the wrong type.

Sometimes when you forget a required parameter you will get a message that looks like Figure 24. In this case I forgot to include the column_index_number in the VLOOKUP statement. If you do not see right away what is wrong with the function, simply choose Help. Then search for the name of the function that you are using and look to see what the parameters are.