# Excel 97 Formulas and Functions

## Excel 97 Formulas and Functions

### Objectives:

- Use
**Formulas**in EXCEL - Use
**Copy**and**Paste** - Use
**Functions**in EXCEL **Format**numeric data

### Open an existing worksheet

If your worksheet is not open,

- Choose
**File / Open**The Open dialog box appears.- Select the appropriate
**drive**,**folder**, and**filename** - Click
**OK**

- Select the appropriate

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 Alaskas January temperature (located in cell B3) and Michigans January temperature (located in cell B4) and place the result in cell B5.

- Click in
**cell B5**or whatever cell is below the January temperature for Michigan in your sheet. - 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 (=). - Press
**Enter/Return**Note: The result**-4**is returned. - Change Alaskas January temperature to
**-6** - Press
**Enter/Return****Note**: The result 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.

- Click in
**cell B5**or whatever cell contains the January average. - Choose
**Edit / Copy**-or-Click on the**Copy**button. - Click in
**cell C5**or whatever cell contains the February average. - 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.

- Click in
**cell B5**or whatever cell contains the January average. - Choose
**Edit / Copy**-or-Click on the**Copy**button. - In row 5,
**select cells**D1 through M1.(All the**cells**in the “average” row with no formula.) - 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 spread 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.

- Click in
**cell N3**or whatever cell is to the right of the December temperature for Alaska in your sheet. - 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 Januarys temperature and Decembers temperature and ignore February, March, etc. - 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,

- Choose
**Help / Contents and Index** - Select Index Tab
- Type
**Functions** - Click on
**overview** - Click
**Display button** - Select from the list of
**types of worksheet function**categories. - Click
**Display button**

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.

- In row 5, select cells B5 through M5.

(All the**cells**containing averages in row 5.) - Choose
**Format / Cells** - Select the
**Number Tab**. (Shown below.)- Choose Category
**Number** - Select zero (0) for Decimal places.
- Click on comma “,” separator check box, if you choose.
- Select a display format for negative number.

- Choose Category
- Click
**OK**Note: All numbers are rounded to whole numbers on the display.