Functions
Some of the more involved formulas may get very long and time consuming to type on. Excel therefore provides shortcuts in the form of functions, which are pre0defined mathematical strings which contain formulae and are named by a term which best describes their use:
Common functions
=SUM (??:??): Totals the contents of a range of cells (see below)
=AVERAGE(??:??): Calculates the average of the contents of a range of cells
=SQRT(??): Calculates the Square rRot of the contents a cell
=COUNT(??:??): Counts the numbers of values within a cell range
=COUNTA(??:??): Counts the number of text entries within a cell range
A full list of the functions available in Excel can be seen in the Paste Function dialogue box (see below)
Cell Ranges
Cell range consists of two or more cells, and includes the values in the first cell right through to the values in the last cell, for example:
=SUM(A1:A10)
This function would add all the values contained in cell A1 all the way through to cell A10 and the cells in between.
Cell ranges are shown with a colon between the first cell in the range and the last cell in the range.
Entering a formula manually
- Click in the cell on which you wish to see the formula
- Type in an equals sign =
- Enter the rest of the formula, i.e. the arguments
- Press the Return key or click on the green tick to the left of the Formula Bar
Using the Paste Function
- Click into the cell in which you wish to se the formula
- Click on the Paste Function icon on the standard toolbar
- Select the category you require in the left of the screen, then select the particular function from this category
- Click on OK
- The Formula Palette appears in the screen
- The Palette shows the function selected (in this case Average) the cell range involved, the actual values involved and the current result. It also shows a definition of the formula used.
- The drop down arrow to the right of the selected function enables you to change the function if required, e.g. you could change to a Sum function from the Average
- To edit or view the current cell range without losing the Palette, click on the red arrow to the right of the cell range. To return to the Palette, click on this button again (its appearance has changed slightly)
- Once you are happy with the formula, click on OK, The function will be displayed on the Formula Bar as normal.
- Having clicked on OK, you can edit the formula by clicking on the bold equals sign to the left of the formula on the Formula Bar. This will display the Palette once again, and the relevant changes can then be made.
Exercise
Perform the following calculations in your ‘income and expenditure’ spreadsheet:
Add up the contents of cells B3 to B7
Find the average of cells B6 to E6
Find the square root of cell D7
Count the number of values in the cell range B3 to E7
Autosum
The Autosum button 
will sum a selected range of cells automatically:
Click in the cell in which you wish to see the total
Click twice on the Autosum button
The total will automatically be inserted into the active cell.
NB. If there is a label or blank cell within the cell range to be totaled, Excel will stop at this cell as it cannot add blank cells or labels to the total. In the above example, if B4 was an empty cell, only the contents of cells B7, B6 and B5 would be added together. Therefore always show zero values with a zero (0), NOT A BLANK cell.
Exercise
Use the Autosum button to add the following cell ranges (on row 8):
C3 to C7
D3 TO D7
E3 TO E7
Complete the Totals in Columns F
Enter the Grand Total in cell B11
Calculate the Income minus total Expenditure in cell B12
