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, in-class 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 RAND function to create a list of random numbers.

Use the paste-special 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.

Videos:

Dialog Box for the Windows/PC users

About the Insert Function dialog box   4:47

Function Basics for the MAC

AutoSum Icon

Rounding

Excel Functions

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 pull-down 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].

 

This is a picture of what the Autosum icon drop down manu looks like. This is a picture of what the functions icon next to the formulas bar  looks like. It also shows the dialog box that opens when you click on the icon.

How to use the Functions dialog box

How-to 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:

Function Basics for the MAC

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

  1. Open a new Excel file. Select Sheet1.
  1. In the range A1:C3 type in the numerals 1 to 9 to recreate the numeric keypad.

 

 

A

B

C

D

1

7

8

9

 

2

4

5

6

 

3

1

2

3

 

4

 

 

 

 

 

  1. Click in cell D1 and click on the AutoSum icon [ALT  H  U] in Excel 2013 use [ALT  H  U  S]. You will see =SUM(A1:C1) as a prompt. Excel is asking if you want to add up the cells in the row (range) A1 to C1. This is what you want to do. Press Enter. You should see the value 24 in cell d1.
  1. Click in cell D4 and click on the AutoSum icon [ALT  H  U] in Excel 2013 use [ALT  H  U  S]. You will see =SUM(D1:D3) as a prompt.  Excel is asking if you want to add up the cells in the column (range) D1 to D3. We want to include all of the cells in the keypad range.  To change the prompt, click in cell A1 and drag across and down to include all of the cells in the range A1 to C3. Now press Enter. You should see the value 45 in cell D4.
  1. Click in cell D4. From the pull-down menu next to the AutoSum icon [ALT  H  U] select Average. in Excel 2013 use [ALT  H  U  A]
  1. Change the prompt to select the A1:C3 range. Press Enter. You should see the value 5 in cell D4.
  1. Put the cursor in cell D4. From the pull-down menu next to the AutoSum icon [ALT  H  U] select Count. in Excel 2013 use [ALT  H  U  C]
  1. Change the prompt to select the A1:C3 range and press Enter. You should see the value 9 in cell D4. Nine cells contain a number. If you edit cell A1 to delete the 7 what happens in cell D4?
  1. What happens to the value in cell D4 if you enter a letter in cell A1? Change cell A1 back to a 7.
  1. Put the cursor in cell D4. From the pull-down menu next to the AutoSum icon [ALT  H  U] select Min. in Excel 2013 use [ALT  H  U  I]
  1. Change the prompt to select the A1:C3 range. Press Enter. You should see the value 1 in cell D4. This is the smallest number in the range.
  1. Put the cursor in cell D4. From the pull-down menu next to the AutoSum icon [ALT  H  U] select Max. in Excel 2013 use [ALT  H  U  M]
  1. Change the prompt to select the A1:C3 range. Press Enter. You should see the value 9 in cell D4. This is the largest number in the range.

Date and Rounding Functions

  1. Select cell A5. Type =TODAY() and press Enter. You will see today’s date. Enter =NOW() in cell B5 to see the date and time.
  1. Select cell A7. Type =PI() and press Enter.

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].

  1. Press Ctrl + 1. Format cell A7 as a Number format showing 6 decimal places and press Ok. You should see the numerical value 3.141593.
  1. Mouse over cells A7:B9. Right click and choose Format cells.  Format cells A7:B9 as a Number format showing 4 decimal places. You will now be seeing 3.1416 in cell A7.

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

  1. Select cell A8. Type = A7*10 and press Enter. You should see =31.4159.
  1. Select cell A9. Type = A7*100 and press Enter. You should see =314.1593.

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 

  1. Select cell B7. Type = ROUND( A7,4)  and pressing Enter.  You should see 3.1416.
  1. Select cell B8. Type = B7*10 and press Enter. You should see =31.4160.
  1. Select cell B9. Type = B7*100 and press Enter. You should see =314.1600.

Random Numbers

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

  1. Select cell F1. Type =RAND() and press Enter. A number between zero and one, showing several digits, will appear in the cell.
  1. Select cell F1. Copy and paste the function into cells F2:F12. You should see different numbers in each cell. Did your starting number in F1 change? It should have automatically changed.
  1. Select cell G1. Type =ROUND(F1,1) and press Enter. A single-digit number should appear in cell G1 to the right of the decimal point. Did the numbers in column F change? Every time you make a change to the worksheet the RAND function will generate a new value.
  1. Press CTRL+Z to undo your last step. Press CTRL+Y to redo the step. Did you get the same numbers? On a MAC, or a laptop without the CTRL key, you should be able to right-click and choose a corresponding command.

Try undo and redo again to see what happens. Every time you enter new data into the worksheet the RAND function will come up with a different value for its cell.

  1. Copy and paste the ROUND function in cell G1 into cells G2:G12. Note: some students are highlighting the text in the formula bar and copying it. That is how to copy text, but does not work when you are asked to copy and paste formulas. All you need to do to copy the formula is to click on the cell and press Ctrl + C. Other methods of copying and pasting are discussed in the Excel Concepts 1 document. 

Paste Special – Values

Pasting as values will replace formulas and functions with the value of those functions, giving us numbers in column G. It’s just like taking a picture of the numbers and using the pictures in place of the formulas. Let’s try it.

  1. Select the range G1:G12 and press CTRL+C to copy the functions.
  1. Right click and choose Paste Special – Values. This icon for this option has the numerals 123 on it.
  1. Click OK or click on cell G1 and press Enter to paste. You should be left with a list of numbers in the G1:G12 range.

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.

  1. Select the range G1:G12 and press CTRL+C to copy the functions.
  1. Right click and choose Paste Special – Values. (Note: The keyboard shortcut for Paste Special is [ALT+F11]. Then press V for values.) This icon for this option has the numerals 123 on it.
  1. Click on cell G1 and press CTRL+V to paste.
  1. Select the cells in the range F1:F12 and delete the RAND functions, we don’t need them anymore.  For the PC you can click on the Delete key to remove all of the formulas at the same time.  For the Mac you have to hold down the fn key and then press the Delete key.

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

  1. Let’s give a name to the set of numbers in the G1:G12 range. Click on the FORMULAS tab on the ribbon.
  1. Select the range of cells G1:G12.
  1. In the name box, above cell A1, you will see G1. Click in the name box and type MYLIST and press Enter.

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

  1. Select the cell G13.

In the middle of the FORMULAS tab you will see an icon called Define Name [ALT  M  M].

If you right-click 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.

39.   Give cell G13 the name MYTOTAL and click Ok.

Now let’s try applying some standard statistical function to the list of numbers.

  1. Click in cell G13. Enter the Function =SUM(MYLIST). This finds the total of your list.
  1. Click in cell G14. Enter the Function =AVERAGE(MYLIST). This finds the average of the numbers in your list.
  1. Click in cell G15. Enter the Function =MIN(MYLIST). This finds the smallest number in your list.
  1. Click in cell G16. Enter the Function =MAX(MYLIST). This finds the largest number in your list.
  1. Click in cell G17. Enter the Function =COUNT(MYLIST). This finds the number of cells in your list that contain a number.

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

  1. Click in cell G19. Enter the Function =MEDIAN(MYLIST). This finds the value midway between the top and bottom half of your list.
  1. Click in cell G20. Enter the Function =MODE(MYLIST). This finds the number that occurs most frequently in your list.
  1. Click in cell G21. Enter the Function =STDEV(MYLIST). This finds the standard deviation of the numbers in your list.

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

  1. Select cell H1. Enter the formula =G1/MYTOTAL Copy the formula into the range H2:H12.
  1. Select the range H1:H12 and right-click. Choose format cells. Change the number format to percentages and show two decimal places.

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.

 

  1. First we need to set up the worksheet. Starting in cell A12, enter labels as shown in the table.
  1. Resize column A so all the labels in A12:A21 fit inside the column.
  1. Format cells B13 and B15 as a percent style with two decimal places.

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

  1. In cell B13 enter the annual interest rate: 24%. Make sure you format this as a percent, and not as the integer 24.
  1. In cell B14 enter the number of payments per year: 12
  1. In cell B15 enter a formula dividing the annual interest rate by the number of yearly payments. =B13/B14.
  1. In cell B16 enter the amount of each monthly payment: 200
  1. In cell B17 enter the total amount of debt using a negative number: -5000
  1. In cell B18 enter the amount we want to end up with: 0 (zero).
  1. In cell B19 enter an NPER function. =NPER(B15, B16, B17,B18,0)
  1. You should see the number 35 followed by several decimals. Note: If you don’t see this value, check to make sure that the number in cell B17 is formatted as a number, not as a percent. Since we can’t have a fraction of a payment period we need to round the number up to an integer. 
  1. Change the formula in B19 to =ROUNDUP(NPER(B15, B16, B17,B18,0),0)  The second 0 inside the parentheses shows you the number of decimal places to include as the result of your calculation. If this is a zero the result of the calculation is an integer.

What was the sum of all of the payments?

  1. Multiply the amount of one payment times the number of periods. In cell B20 enter =B16 * B19. 

How much interest did you pay?  

  1. Subtract the principal from the total of all payments. In cell B21 enter the formula = B20 + B17. Note that the credit card balance in cell B17 is expressed as a negative number.

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

  1. Change the amount in cell B16 to 100. If you try entering 100 in cell B16 you will get the NUM error message. You will never finish paying off the loan because you are only paying the interest.
  1. Now try entering 101 in cell B16. The number of payments is in cell B19. Enter =B19/12 in cell C19 to show the number of years it will take to pay off the loan.

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%?

  1. Change the numbers in cells B13, B16, and B17 to find the answer.

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.

 

  1. First we need to set up the worksheet. Starting in cell A24, enter labels as shown in the table.
  1. Format cells B28 and B29 as a percent style with two decimal places.

 

 

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.

  1. In cell B25 enter the number of years to pay back the mortgage:  30
  1. In cell B26 enter the number of annual payments: 12
  1. In cell B27 multiply the payments per year by the number of years, =B25 * B26.
  1. In cell B28 enter the annual interest rate:  4%
  1. In cell B29 divide the annual interest rate by the number of yearly payments. =B28/B26.
  1. In cell B30 enter the amount of the loan as a negative number: -100000
  1. In cell B31 enter the amount we will owe at the end of 30 years: 0 (zero)
  1.  In cell B32 enter =PMT(B29, B27, B30, B31,0)

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.


  1. First you tell Excel what cells will remain open (unlocked) for the user to enter data. You will be able to make changes only to the unlocked cells when the worksheet is protected. Select cells B25, B26, B28, B30 and B31 and right-click. Choose Format Cells. Click on the protection tab inside the cell formatting dialog box. Uncheck the box next to “lock cells.” 
  1. Now you protect the worksheet. On the Review tab of the ribbon, click on the Protect sheet icon to protect the worksheet. [ALT  R  P  S]. For a MAC the icon is labeled “Sheet.” In the dialog box, uncheck the Select locked cells” option. The “unlocked cells” option should be checked.  Click on OK

 

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.

  1. Delete the values in the five unprotected cells.

Unprotect the worksheet using the Unprotect Sheet icon [ALT R P S] on the Review tab of the ribbon. 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.

  1. In cell B25 enter the number of years to pay back the mortgage: 15 and press Tab.
  1. In cell B26 enter the number of annual payments: 12 and press Tab.
  1. In cell B28 enter the annual interest rate: 5%, and press Tab.
  1. In cell B30 enter the amount of the loan as a negative number: -300000 and press Tab.
  1. In cell B31 enter the amount we will owe at the end of 15 years: 0 and press Tab.

The monthly mortgage payment, found in cell B32, is: _____________

Staffing Guide Project:

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 efficiently help the customers. We will create a form to calculate the staffing levels needed.

  1. In cells A40, B40, C40 and D40 Enter the labels: Date, Guests, Maids, and Desk Clerks.
  1. In cell A41 enter =Today()+7 This is the date one week from now. 
  1. In cell A42 enter = A41+1. Copy this formula into cells A43:A47.
  1. In the range B41: B47 enter the function =RANDBETWEEN(180,330) This function will return random whole numbers  between 180 and 330.

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

  1. Select the range B41:B47 and copy it. (Ctrl + C). With the cursor still in the B1:B47 range, right click and choose Paste Special. The Paste Special dialog box will open. Click on Values and Click on OK. The formulas we had in those cells are now replaced with numbers.

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.

  1. In cell C41 enter the function =ROUNDUP(B41/15,0) Copy the function into the range C42:C47. 
  1. In cell D41 enter the function =ROUNDUP(B41/60,0) Copy the function into the range D42:D47.

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.

  1. Apply a center format [ALT  H  A  C] to all of the cells in the table A40:D47.

 A picture of the completed staffing guide project.

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.

  1. In cell A50 enter Radar Gun Reading. In cell B50 enter =RANDBETWEEN(50,85)
  1. In Cell A51 enter Speed Limit. In cell B51 enter 65.
  1. In cell A52 enter Fine Amount. In cell B52 enter =IF(B50>65,200,0) Format cell B52 as currency.

The amount of the fine is recalculated every time a new random number appears in cell B50. It can be either $200 or Zero.

Optional - additional course material: XL Stats

You may be using this add-in for your statistics course.

https://help.xlstat.com/customer/en/portal/articles/2062312-running-xlstat-the-first-time-excel-2007-2010-2013-2016-?b_id=9202

Video How to use the XL Stats add-in to generate functions   0:57

https://www.youtube.com/watch?v=IrDxg2XJaaI

 

Review – Fine Points

 

 

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 paste-special 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.