Untitled Document

Working with Formulas

Beginning

Formulas in Excel all begin with an equal sign (=) and can be entered in either of two places:

1. The formula can be entered in the cell it will correspond to

2. A formula can be entered in the formula bar

Note: Figures taken from Microsoft Excel 2000

Formulas can use mathematical operators such as +, -, * , / (÷), or ^ to perform various calculations.

Pressing return/enter after typing the formula will display the result.

Similar to entering data, if you single click the formula cell, any formula will be deleted when you begin typing. Double clicking on the cell will allow you to edit the formula.

References with Formulas

Remember that each cell is an intersection of a row and a column. Thus, each cell has a unique label that is the combination of the row and the column names in that order.

The top left cell in column A and Row 1 is called A1.

The cell underneath A1 is called A2 (column A and row 2).

The cell next to A1 is called B1 (column B and Row 1).

These cell labels can be used in formulas to access the datum in that cell.

You can refer to multiple cells by specifying the first and last cells of the group with a colon in between the cell labels (e.g =sum (A1:A5) See example below)

Notice that the selected cells are A2 through A8 and they are being summed in cell A10

Using Labels

A short cut to get to a cell's label (name):

While typing a formula, click on the cell that you want to refer to and the label will appear in the formula.

You can also refer to multiple cells in this way by clicking on the first cell of the group and dragging to the last cell of the group.

If, while entering the formula, you want to move to another cell rather than reference that cell, you must either hit "enter" to finish the formula or "escape" to cancel.

Using Functions within Formulas

Excel provides a lot of mathematical and statistical equations for your convenience

There are two possible formats for the functions that you can use

Function Name (beginning cell label:end cell label)

Function Name (first cell label, second cell label, third cell label, etc...)

Clicking on on the Excel toolbar will show you a list of all of the functions available (and give you more information about how to use them).

Copying Data into Multiple Cells

To take information from one cell and copy it into other cells:

Select the cell and then,

Click on the little square in the bottom-right corner of the cell.

Drag over the cells that you want to have the datum copied into.

When you let go of the mouse, the information will be automatically copied!

Copying Formulas into Other Cells

Similar to copying data into other cells, formulas can also be copied from one cell into other cells.

Conveniently, this is done in the same manner.

(Insert picture here with two frames (possibly a flash movie) and the following text)

If one of your cells was calulated by a formula, simply select that cell,

then click on the little square in the bottom-right corner of the cell

Drag over the cells that you want to have the formula copied into.

If the formula contains a label, then either the row or column part of the label will be incremented automatically for each new formula (column if you move right or left, row if you move up or down)

Results from formulas

Once you have caluculated a formula and have a result in one or more cells be aware that if you change your data then the calculated result will also be changed.

For example,

if my data looked like this:
	And I found the average of all the data from B2 to B9 (written in cell B11)

Watch what happens if the score in cell B7 is changed from 32 to 95:

The average automatically changes!