Excel – Basic Functions
This lesson introduces the use of Functions in
Excel.
Instructions:
Read through
the set of learning objectives. This is what you should know when you finish
the lesson.
Watch the
YouTube videos before starting the lesson.
Complete every
step in the assignment, in the order given. Do not skip steps.
When you have
done all of the steps, fill out the Check for Understanding (CFU) form.
A completed
CFU for every lesson, inclass and homework, is due to be turned in during your
next class.
The CFU forms
are not to be submitted by email.
Where there
are differences between software versions, the instructions for Excel 2010 are
in Black, for the Mac are in green and for Excel 2013 are in blue.
Summary of Key Points:
Use the AutoSum icon functions SUM, AVERAGE, COUNT, MINIMUM, and MAXIMUM.
Use date and rounding functions.
Use the
Use the pastespecial option to replace formulas with their values.
Define a name for a range of cells and use it in a function.
Use the NPER function to find the number of monthly payments needed to pay off a loan.
Use the PAYMENT function to find the monthly amount needed to pay off a loan.
Protect cells to make data entry easier.
Dialog Box
for the Windows/PC users
About the Insert Function dialog box 4:47
Functions are
small computer programs that are built in to Excel to help you quickly perform complex
calculations. To use a function, you type in the = sign, the name of the
function, and a set of parentheses ( ). For some functions that is enough to
generate a result. For example, =PI( ) will show
3.14159 and =TODAY() will show the current date. The names of the functions are
not case sensitive. =TODAY() and =today() will
generate the same result. For clarity I use capital letters for the names of
functions.
Most functions require additional data (values) to perform a
calculation. Each piece of data is called an argument. An argument can be text, a number, a
formula (equation), a reference to a cell(C7), or a
reference to a range (group) of cells(D7:G10). The arguments are entered
between the parentheses.
If there is more than one argument, commas are used to separate them.
For example =SUM(7,8,10) or =AVERAGE(B5,C7, C8, F4).
If the argument is a reference to one or more cells, Excel will use
the values entered into those cells when it evaluates the function. Empty cells
are counted as zeroes.
The advantage of using cell references as arguments is
flexibility. Instead of writing a
new function every time you change one of the argument values you can simply
enter the new value into the referenced cell and Excel will automatically
generate a new result for the function. For example =SUM(D7,E7,F7) would generate a new total whenever a
number in cell D7, E7 or F7 was changed.
To find a function
click on the fx icon above the worksheet.
The Insert Function dialog box will open. Another method to open this dialog
box is to open the pulldown menu next to the AutoSum icon [ALT H U] and click on More Functions. A third method is to use the fx icon on the Formulas ribbon [ALT M F].
How to use the Functions dialog box
Howto Video Dialog Box
for Windows/PC users. The
functions dialog boxes for Mac are different for those for the windows PC
version of Excel. This video demonstrates the MAC version:
If you don’t know the name of the function for what you are trying
to do, you can type a description of your goal into the first text box. Excel
will show you a short list of functions that might work. Try typing
“Mortgage payment” (without the quotation marks) and clicking on
the GO icon.
You can also generate a list of specialized functions by selecting a
category box. Two Financial functions we will be using are NPER and PMT so when
looking for those functions you would choose Financial. To see a complete list
of functions you would choose All. Note: You can access Financial and other
function categories directly from icons on the left side of the Formulas tab [ALT M].
When you select a
function you will see a list, below the textbox, a description of what the
function is used for, the required arguments, and an icon for Help on this
function.
You don’t
always have to use the dialog box. You can also type the function directly into
a cell or use the AutoSum icon [ALT H U] in Excel 2013 use [ALT H
U S]. You can also use the shortcut, ALT + =,
to create the SUM function if your cursor is positioned in an empty cell below
a column of numbers, or to the right of a row of numbers.
If the function has an argument that requires that you select a range
of cells, you will see a text box with a red icon at the right end of it. There are three of them in the picture.
The icon is a toggle switch to collapse the dialog box to make it easier to see
the underlying worksheet. Clicking again restores the dialog box to full size.
In this course I am calling this icon the “Cell Selector
icon.” You do not need to
click on that icon to select ranges of cells.
To select a range click on a worksheet tab, drag the mouse over a
group of cells, and press Enter. You can also type the name of the range, if
you have given it a name, directly into this box. Range names are discussed in
the Toolkit lesson and in the Formulas lesson.
The AutoSum icon – Sum, Average, Count, Min, Max.
Video
demonstration of the AutoSum icon: AutoSum Icon



A 
B 
C 
D 
1 
7 
8 
9 

2 
4 
5 
6 

3 
1 
2 
3 

4 















Date, Row, and
Rounding Functions





How many decimal
places you see will depend on how the cell is formatted. You can change the
number of decimal places that are showing by clicking on the Increase Decimals
icon [ALT H 0] or on the Decrease Decimals
icon [ALT
H 9]. Note that the
keyboard shortcut to decrease the number of decimal places only works when the
number has decimal places showing.


Changing the number of decimals places
that are displayed does not change the value of the formula.


To change the value
to remove the extra digits instead of just hiding them you can use a ROUND
function. The ROUND function is
often used for calculations involving dollars and cents, where only values of
two decimal places or zero decimal places are needed. You would also use it
when you are counting discrete items or things. You can’t have 3.14
children or 1.43 pets.
Video on
rounding: Rounding



Random
Numbers
We want to set up a list of random numbers and then perform statistical functions on the list of numbers.





Paste Special – Values
Copying the contents of a cell and pasting it back into the cell, this time formatted as a value, replaces the formula or function with its numerical value. Numbers are stable. The results of formulas are dynamic; the vales change when changes occur in other cells in the worksheet. Yyou often want to remove the formulas, once you have generated the desired results, to make sure the initial numbers are not altered by later changes to the worksheet.



Let’s try it again. Click on cell G1 and enter the function =ROUND(F1,1)*10. Copy and paste that function into cells G2:G12. 




You should be left with a column of 12 numbers in the G1:G12 range. If you get an error message, something went wrong when you tried to paste special as values. Undo a few steps and try again until you get it to work right.
Using Range Names in Functions



There are two other ways to create a name for the range. You can even give a range name to a single cell.

In the middle of the FORMULAS tab you will see an icon called Define Name [ALT M M].
In 2013/16 the keyboard shortcut is [ ALT M M D]
If you rightclick you will also see the option Define Name.
Selecting either one of the Define Name options will open a dialog box for creating range names.
For a Mac you
would click on Insert, then Name, then Create to open the dialog box.
You can also click on the Range Name Manager icon [ALT M N] and select New from that dialog box.
42.
Give cell
G13 the name MYTOTAL and click Ok.
Now let’s try applying some standard statistical function to the list of numbers.





The functions we just entered into cells G13 to G17 can all be found on the dropdown menu next to the AutoSum icon. [ALT M U].



Finally, let’s show the values in MYLIST as percentages.


Financial
Functions
We want to know
how long it will take to pay off a credit card if we owe $5000 and can make
payments of $200 per month. The annual interest rate on the credit card is 24%.
You would use the NPER (number of periods) function to solve this problem.
The NPER function has five arguments:
Rate –
the interest rate per period. In our example that would be the annual rate of
24% divided by 12 months to find the rate for one month.
Pmt –
The amount of the payment. Enter a positive value.
PV –
Present Value, the amount we owe. This is entered as a negative number. Be
careful. If you enter this as a positive number you will get the wrong result.
FV –
Future value. We want to reduce our debt to zero.
Type is when
the payment is made, either at the start of the period or at the end. Use a 0 for payments made at the end of
a period. Use a 1 for payments made at the beginning of a period. We make our
payments at the end of the period so this argument should be zero. If you
don’t enter a value for this argument Excel uses zero.


Warning – Excel may have ”helped
you” by also changing the format of cell G14. If it showing as Percent
style change the format back to General. 

A 
B 
12 
Number of Periods to Pay Credit Card Debt 

13 
Annual Interest Rate 

14 
Number of
Payments per Year 

15 
Interest Rate per Period: 

16 
Payment Amount per Period: 

17 
Present Value: (What you owe) 

18 
Future Value: 

19 
Number of Payments: 

20 
Total Value of Payments: 

21 
Total Interest Paid: 

How many months will it take you to pay
off your credit card debt of $5,000 if you pay $200 per month ($200) and the
annual interest rate is 24%? To answer this we need to enter this data into the worksheet









What was the sum of all of the payments?

How much interest did you pay?

How long would it take you to repay the
loan if you paid just the interest amount, $100 per month?


How many months will it take you to pay
off your credit card debt of $15,000 if you pay $600 per month and the annual
interest rate is 25%?

Periodic Payment (PMT)
We are borrowing
$100,000 to purchase a house. The annual interest rate is 4% and we will take
30 years to pay off the mortgage. We want to know how much our monthly payment will
cost. We will use the PMT (payment amount) function to solve this problem.
The PMT function
has five arguments:
Rate –
the interest rate per period. In our example that would be the annual rate of
4% divided by 12 months to find the rate for one month.
Nper is the total amount of payments. In our
example we would make 12 payments per year for 30 years for a total of 360
payments.
PV –
Present Value, the amount we owe. This is entered as a negative number. Be
careful. If you enter this as a positive number you will get the wrong result.
FV –
Future value. We want to reduce our debt to zero.
Type is when
the payment is made, either at the start of the period or at the end. Use a 0 for payments made at the end of
a period. Use a 1 for payments made at the beginning of a period. We make our
payments at the end of the period so this argument should be zero. If you
don’t enter a value for this argument Excel uses zero.



A 
B 
24 
Amount of a Monthly Mortgage Payment 

25 
Number of Years 

26 
Payments per Year: 

27 
Number of Payments: 

28 
Annual Interest Rate: 

29 
Interest Rate per Period: 

30 
Present Value: (amount you owe) 

31 
Future Value: 

32 
Payment per Period: 

You want to get a mortgage to buy a house that costs $100,000. The
bank charges 4% on the mortgage and is willing to extend you the loan for 30
years. They ask for monthly mortgage payments. How much is the monthly mortgage
payment? To solve this
problem enter the values from the question into the worksheet.








The monthly
mortgage payment is: _____________
We can set up the worksheet so our formulas and labels are protected
so users can enter data but can not change our labels or formulas. This also
lets us move the cursor directly from unlocked cell to unlocked cell, skipping
the cells in between.

Click on the tab key several times. The
cursor will move through the five unprotected cells. This helps the user enter data into
the right locations. 
Unprotect the worksheet using the
Unprotect Sheet icon on the Review tab. [ALT R P S] For 2013/16 [ALT R P P S ].
For
a MAC use the Sheet icon on the Review tab. Note: this is the same icon we used to
protect the sheet. Clicking this icon switches protection on and off. 
You want to get a mortgage to buy a house that costs $300,000. The
bank charges 5% on the mortgage and is willing to extend you the loan for 15
years. They ask for monthly mortgage payments. How much is the monthly mortgage
payment? To solve this problem enter the values from the question into the
worksheet.





The monthly mortgage
payment, found in cell B32, is: _____________
PREVIEW
You can use the RANDBETWEEN function to test formulas and functions. The IF Function lets you use more than one formula in a cell. You’ll learn more about this is the IF Functions lesson.
Here’s an example.



The amount of the fine is recalculated every time a new random number
appears in cell B50. It can be either $200 or Zero.
(The rest of the assignment is optional material you are not required to
complete.)
Practical application  Staffing Guide:
You are the general manager of a hotel
with 330 rooms. The reservations
manager has given you her forecast for how many guests will be in the hotel
next week. You want to make sure
you have enough employees working to handle the customers efficiently. We will
create a form to calculate the staffing levels needed.




After the random numbers have been generated, we want to replace the formulas with
their values. In a real form we would get these
numbers from the reservations manager

One maid can
clean 15 rooms during an eight hour shift. One desk clerk can handle the needs
of 60 customers. Calculate how many
employees are needed for each day.


Why did we use roundup and not round? If
we rented 277 rooms the ROUND function would tell us
we would need 18 maids. But 18 maids can only clean 270 rooms. We would have nobody
to clean the seven remaining rooms.

Optional  additional course material: XL Stats
You may be using this addin for your statistics course.
Video How to use the XL Stats addin to generate functions 0:57
https://www.youtube.com/watch?v=IrDxg2XJaaI
Instructions for the Check for
Understanding (CFU) form.
A CFU form is turned in for every assignment.
Starting with the line for your name, copy and paste the CFU form into a Word document.
Fill
in your name, date, section number
Circle
(or indicate by highlighting) a Y or N response for each statement. I do not
subtract points for No answers.
Below the list
of Y/N statements, insert ONE screenshot
of any part of your work. I just need to see something to prove that you did
the assignment. I don’t need to see all of the assignment. Resize the
screenshot, if necessary, so both the form and graphic can fit on one page.
A copy of the CFU is due to be turned in during the next
class session. I do not accept
CFU’s through email.
Name _______________________________
Date___________ Section
_______
Check for
Understanding – Excel  Basic Functions
Y/N I can use the AutoSum icon functions SUM, AVERAGE, COUNT, MINIMUM, and MAXIMUM.
Y/N I can use date and rounding functions.
Y/N I can use the Rand function to create a list of random numbers.
Y/N I can use the pastespecial option to replace formulas with their values.
Y/N I can define a name for a range of cells and use it in a function.
Y/N I can use the NPER function to find the number of monthly payments needed to pay off a loan.
Y/N I can use the Payment function to find the monthly amount needed to pay off a loan.
Y/N I can protect cells to make data entry easier.