Excel - Formulas and Functions

Objectives:

1. Use Formulas in EXCEL
2. Use Copy and Paste
3. Use Functions in EXCEL
4. Format numeric data

Open an existing worksheet

If your worksheet is not open,

1. Choose File | Open.

The Open dialog box appears.

1. Select the appropriate drive, folder, and filename.
2. Click OK.

Top of Page

Using Formulas in your worksheet

Your sheet will look like the one illustrated below when you complete this activity.

To enter a formula you need to know the calculation needed and the cells to be used in the calculation. In this example, we will calculate the average of the January temperatures in Alaska and Michigan. We will use Alaska’s January temperature (located in cell B3) and Michigan’s January temperature (located in cell B4) and place the result in cell B5.

1. Click in cell B5 or whatever cell is below the January temperature for Michigan in your sheet.
2. Type the formula =(b3+b4)/2.

Note: The equal sign (=) is critical.
Without it, Excel does not know that you want to perform a calculation.
All formulas and functions must be preceded with an equal sign (=).

3. Press Enter|Return.

Note: The result -4 is returned.

4. Change Alaska’s January temperature to -6.
5. Press Enter|Return.

Note: The result 3 is returned.

You did not need to re-enter the formula or recalculate the result. Excel did that for you.

Top of Page

Using Copy and Paste

You need to enter the same formula for February, March, April, . . . December. That can be very time consuming.

Instead of re-entering the formula, we will copy and paste.

1. Click in cell C5 or whatever cell contains the January average.
2. Choose Edit | Copy.
   

-or-
Click on the Copy button.  

3. Click in cell C5 or whatever cell contains the February average.
4. Choose Edit | Paste.
   

-or-
Click on the Paste button.  

Note: The result 20 is returned.
You did not need to re-enter the formula or recalculate the result.
When Excel copied the formula for you it determined that this time you wanted to calculate data in column C.

Using Copy and Paste - (More)

You still need to copy the formula to March, April, . . . December.
Even with copy and paste that could be very time consuming.
We will copy from one cell and paste to many cells.

1. Click in cell B5 or whatever cell contains the January average.
2. Choose Edit | Copy.

-or-

Click on the Copy button.

3. In row 5, select cells D1 through M1.

(All the cells in the "average" row with no formula.)

4. Choose Edit | Paste.

-or-

Click on the Paste button.

Note: The formula was entered in all selected cells.

You did not need to re-enter the formula or recalculate the result.

Top of Page

Using Functions

Many times you use a spreadsheet to handle large quantities of data. If you had to calculate the average annual temperature in Alaska, you could enter the formula

=(B3+C3+D3+E3+F3+G3+H3+I3+J3+K3+L3+M3)/12.

However, this would be a lot of typing.

Instead, you could use a function. Excel has hundreds of functions available for you to use. The function we need is the AVERAGE function.

Using the AVERAGE function, you indicate the starting cell and ending cell addresses, but not all the addresses in between.

1. Click in cell N3 or whatever cell is to the right of the December temperature for Alaska in your sheet.
2. Type the formula =AVERAGE(B3.M3)

-or-

Type the formula =AVERAGE(B3:M3)

-or-

Type the formula =AVERAGE(B3..M3)

Note: The equal sign (=) is critical.

Also, the period (.) or colon (:) or double dots (..) each tell Excel to average the RANGE of numbers between B3 and M3.

The function =AVERAGE(B3,M3) with the comma separating the two cell addresses would tell Excel to average January’s temperature and December’s temperature and ignore February, March, etc.

3. Press Enter|Return.

Note: The result 30.75 is returned.

Calculate the average for Michigan by entering the function or by copying and pasting.

Functions Available with Excel

To see a complete list of the worksheet functions available with Excel,

1. Choose Help | Contents and Index.
2. Select Index Tab
3. Type Functions.

Top of Page

Formatting Numeric Data

Note that some averages are whole numbers and some are decimal fractions. We would like all the numbers to appear as whole numbers on our sheet.

1. In row 5, select cells B5 through M5.
   (All the cells containing averages in row 5.)
2. Choose Format | Cells.
3. Select the Number Tab. (Shown below.)


1. Choose Category Number.
2. Select zero (0) for Decimal places.
3. Click on comma "," separator check box, if you choose.
4. Select a display format for negative number.
4. Click OK.

Note: All numbers are rounded to whole numbers on the display.

Save your worksheet

Top of Page