Chapter 5 Spreadsheet Functions

Contents
CHAPTER 5 SPREADSHEET FUNCTIONS ..................................................................................1
SPREADSHEET FUNCTIONS ........................................................................................................2
PMT FUNCTION .............................................................................................................................2
VLOOKUP FUNCTION ...................................................................................................................4
IF FUNCTION ..................................................................................................................................6
NESTED IF STATEMENT ............................................................................................................10
COMBINING FUNCTIONS TOGETHER.....................................................................................11
CELL PROTECTION .....................................................................................................................13
GRIDLINES ....................................................................................................................................14
NAMING AND DELETING SHEETS...........................................................................................15
REFERRING TO CELLS IN A DIFFERENT SHEET ..................................................................15
WHAT’S WRONG WITH THIS FUNCTION ...............................................................................16
Spreadsheet Functions
There are several hundred functions available to you in a spreadsheet. The
easiest way to learn all of them is to choose the Sum Icon to get the drop down
menu of the most common functions and choose More Functions … at the bottom of
the list. This should give you an alphabetical listing of all the functions (you may need to
select change the
category to All. 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.
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. We do not want to use

commas when writing this number since the computer would think that we
are separating parameters.

Note: The payment function will return a negative number. 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 B1

=PMT(B2/12,B4,B1)

If you want the display to be positive you can use the ABS (Absolute Value)
function like this:

=ABS(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 bank1? How much in interest will you pay on this
loan2?

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

1
You just need to multiply the number of payments * the payment amount.
2
Take the amount that you will pay the bank and subtract the amount that you borrowed.
 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

B1

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 (See Chapter 4)
which is 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 student‟s 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

B1

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 as
shown above. 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 Home Tab, Cells Group, Format, Lock Cell. By
default the cell is Locked (hence the yellow color). When
the cell is unlocked the yellow is gone.

Step two: Is to turn the protection feature on. This is done through Home Tab, Cells
Group, Format, . 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. This option
will change to when the spreadsheet is protected. For this
class you should leave the first two check boxes checked.

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.

When someone tries to change a cell that is locked, a message box appears and
says that you can not change a locked cell. You will also notice that a lot of the icons
and options in the pull down menus have become grayed out, meaning they are not
available because you have protected the document.

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 Page Layout Tab, Sheet Options Group, Print Check box. You can choose to
not show the gridlines on the screen as well. In this case the gridlines will be showing
on the screen but not printed.

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 double click on the sheet name
and then type in the name you want to
give it.

You should also delete the extra

sheets that you are not using. On the
Home tab, Cells Group, Delete,
.

You can also copy a sheet that you

have completed. On the Home tab, in the
Cells group, click Format, and then under
Organize Sheets, click Move or Copy
Sheet. 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 principle amount in the PMT
function. If you do not see right away what is wrong with the function by looking at the
help tip then try looking the function up to get more detailed information.