04 Using Formulae and Functions

Excel is a spreadsheet programme that allow you to do more than just present numerical data in tables. It also allows you to create mathematical formulas and execute functions and this handout will show you how to create and use these.

Basic Formulae

Excel enables you to perform calculations ranging from adding the values of two cells together to performing more complex calculations. Formulae are entered in worksheet cells and always begin with an equal sign "=". Each formula then includes the addresses of the cells whose values will be included in each formula with mathematical operands placed in between. After the formula has been typed into the cell, the calculation can be performed immediately and the formula seen in the formula bar.

To add, subtract, multiple or divide two cell values:

1. Click on the cell where the result is to appear, e.g. E2.
2. To begin the calculation type =
3. Type in the cell address of the first cell, e.g. B2.
   The first cell will have a (blue) coloured border and the cell address in the formula will be shown in the same (blue) colour.
4. Type in + to add; - to subtract;* (SHIFT+8) to multiply; / to divide
5. Type in the cell address of the second cell, e.g. C2.
   The second cell will have a (green) coloured border and the cell address in the formula will be shown in the same (green) colour.
   The formula will appear in both the results cell and the formula bar.
   The illustration shows cells B2 and C2 being added together:



6. Press the ENTER key.
7. The result 7 will appear in the designated cell.

NOTE: Cells in formulae do not have to be adjacent to each other. They can be anywhere on the worksheet. You just need to enter each cell reference correctly.

Copying and Pasting formulae

It is likely that on a worksheet the same calculations are to be performed on different sets if numbers. Rather than having to enter the same formulae in to different cells it is quicker to copy a formulae and paste it in to a new cell.

The illustration above shows the sum of office hours and over time worked in the 'Total hours' column for each day of the week. The formula in cell D2 is "=B2+C2", in cell D3 is "=B3+C3" and in D4 is "=B4+C4". A similar formula is required in cells D5, D6, D7 and D8. Rather than rewriting the formula in each of these cells it is possible to copy the formula from cell D2, D3 OR D4 to each of these cells. Excel will make a slight change to the formula so that it corresponds to the B and C columns but uses figures from the correct row, i.e. the formula in D5 will be "=B5+C5".

To copy and paste a formula from one cell to another:

1. Click on the cell containing the formula to be copied. A black border will appear around the cell.
2. Click on the Copy tool on the standard toolbar:
   The black border will become a moving dotted border.
3. Click on the cell to copy the formula in to. A black border will appear around the cell.
4. Click on the Paste tool on the standard toolbar:
5. Press then ENTER key.

To learn how to peform calculations using figures on different worksheets click here.

Relative, Absolute and Mixed Cell Referencing

Each cell has a reference and these cell references are used in formulae. However, formulae can include relative reference, absolute referencing and mixed referencing.

Relative referencing

To call a cell by its column and row label, such as "A1", is called relative referencing. When a formula contains relative referencing and is copied from one cell to another, Excel does not copy the formula in to the new cell exactly. Excel changes the cell address relative to the new cell and its row and column labels. So in the example at the top of this document, the formula entered into cell D2 is "=B2+C2" and when it is copied in to cell D3, then Excel will change the formula to "=B3+C3".

Absolute referencing

If a cell reference is to remain unchanged when copied to a new cell is called absolute referencing. A dollar sign "$" placed immediately before the row or column label indicates that that reference is absolute and should not be changed when the formula is copied to another cell. So using the above example, if the formula in cell D2 is "=$B$2+$C$2" and is copied to cell D3, the formula will be copied exactly. In other words, the formula in D3 will also be "=$B$2+$C$2".

Mixed referencing

Mixed referencing can be used where only the row OR column must remain unchanged when a formula is copied to a new cell. For example, in the formula "=(B$2+$C2)", the row of cell B2 is fixed and the column of cell C2 is fixed.

Basic Functions

Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure. Functions can be used to perform simple or complex calculations. The functions immediately available on the standard tool bar include the Sum, Average, Count, Max and Min functions.

In the illustration below cell C9 has been chosen to show the sum of the values in the cells ranging from to C2 to C8. When the AutoSum button has been activated the cell address window at the top left will contain the function name SUM, a blue dotted border will appear around the range of cells containing values to be summed, the formula =SUM(C2:C8) will appear in the formula bar with a : between the two cell references indicating a range of cells, and the also in the cell where the result will appear:

In this example the formula shows that the correct cells have been identified. When the ENTER button on the keyboard is pressed the result will appear in the selected cell:

To perform an AutoSum function:

1. Click on the cell where the result is to appear.
2. Click on the AutoSum button on the standard toolbar.
   A formula will appear in the formula bar and the result cell.
3. Ensure that the formula shows the correct range of cells (: indicates range).
4. Press the ENTER key on the keyboard.
   The result will appear in the designated cell.

To find the average value of a range of two or more cell values:

1. Click on the cell where the result is to appear.
2. Click on the black triangle to the right of the AutoSum button on the standard toolbar.
   A pop-down menu will appear:

   

3. In the pop-down menu click on Average.
   A formula will appear in the result cell and in the formula bar.
   The structure of the formula will be the same as the sum formula above except the word sum will be replaced with the word you chose from the pop-down menu, i.e. AVERAGE.
   Ensure that the formula shows the correct range of cells.
4. Press the ENTER key on the keyboard.
   The result will appear in the designated cell.

To find the number of cells in a range of cells:

1. Click on the cell where the result is to appear.
2. Click on the black triangle to the right of the AutoSum button on the standard toolbar.
3. In the pop-down menu click on Count.
   A formula will appear in the result cell and in the formula bar.
   Ensure that the formula shows the correct range of cells.
4. Press the ENTER key on the keyboard.
   The result will appear in the designated cell.

To find the maximum or minimum cell value in a range of cells:

1. Click on the cell where the result is to appear.
2. Click on the black triangle to the right of the AutoSum button on the standard toolbar.
   In the pop-down menu click on either Max on Min.
   Ensure that the formula shows the correct range of cells.
3. Press the ENTER key on the keyboard.
   The result will appear in the designated cell.

To perform more complex functions using the Insert Function box click here.

Exercises

For exercises to practice the above click here.

Back to Excel Home Page