A function is an inbuilt utility that simplifies the creation of arithmetic expressions involving cell ranges.

For example, we could add up the contents of all the cells in the range B4: D7 with the expression:

=B4+B5+B6+B7+C4+C5+C6+C7+D4+D5+D6+D7

Obviously, the bigger the cell range we wish to add the more terms there will be in the expression.

The SUM function simplifies the process considerably. All we have to enter is the function:

=SUM(B4:D7)

The SUM function can be used in an arithmetic expression as shown in the following examples:

=SUM(C4:F8) + B6

=B6 – SUM(B7:B12)

=SUM(A3:B6) + SUM(C3:D9)

Syntax

The syntax of a function is the formal structure of the function. We write the syntax of the SUM function as:

=SUM(range)

Where range is the range of cells that are being averaged as, for example, B4:D7.

Syntax: =AVERAGE(range)

AVERAGE functions in exactly the same way as SUM, except that it calculates the average value of the cells in range.

Syntax: = MAX(range), =MIN(range)

These functions determine the maximum and minimum values in a range of cells.

Syntax: =COUNT(range)

COUNT returns the number of cells that contain values in range.

=AVERAGE(range) is actually the same as =SUM(range) / COUNT(range).

Often functions are used to summarise data in a spreadsheet. When you create a spreadsheet, it is good practice to put your summary information at the top of the spreadsheet and the data on which the summary is based below.

Suppose we wish to summarise the data in the spreadsheet below.

In this we may wish to find the sum, average, maximum and minimum of each column as well as the number of data entries in each column. We start by inserting four rows below row 2.

The next screen illustrates the situation at this stage. The functions have been displayed in the cells rather than the values. How to do this will be explained shortly.

To display the functions rather than values in the cells:

Where an entry is too wide to display in a cell, a series of hash symbols (#####) will be displayed.

In the above screen, the ##### in cells E6 and E7 indicates that the cell is too narrow to display the contents.

The IF function allows you insert different values, text or formulas in a cell depending on the result of a test.

Syntax: =IF(condition; TrueValue ; FalseValue)

Condition is a test which can evaluate to True or False. If Condition is True, then TrueValue is inserted into the cell. If Condition is False, then FalseValue is inserted into the cell.

Condition generally involves a comparison between two expressions. The comparison is created using one of the operators:

Suppose in the previous example, we wish to indicate whether a score is above average or below average by inserting the appropriate word in the adjacent column. We could proceed as follows:

To understand why we need mixed mode addressing rather than absolute addressing in C9, suppose we were to insert a column after column D and copy column C to the new column. We would get the following:

If we had used absolute addressing, you would then have obtained the entry =IF(D9>$B$7;”Above”;”Below”) when you copied cell B9 to E9. However the average for column D is found in D7 not in B7.