Chapter 4. Formulas and Functions

Arithmetic formulas

Abstract

Generate formulas using cell references and arithmetic operatorsPlus (+), minus (-), division (/) and multiplication (*)

The main purpose of a spreadsheet is to automate numeric work and to store the calculations and their results for later use.

At the heart of any numeric work are the calculations themselves. In their simplest form, these are made up of numbers separated by the usual arithmetic operators of addition, subtraction, division and multiplication. The only difference is that the symbols we use in spreadsheets may be different from those you are used to.

Indicating a calculation

Calculations are done within cells. In order to indicate that we wish to do a calculation in a cell, we preface the cell entry with the = symbol. This indicates to Calc that it must treat the cell entry as a calculation.

Calc records the entry in a cell as you enter it, but normally displays the result of the calculation.

For example, a cell could contain the entry: =7+8

Calc however displays the result 15. You need to make a distinction between the actual contents of a cell and what is displayed.

Arithmetic operators

The four basic operations are as follows:

Addition: =7+8
Subtraction: =7-8
Multiplication: =7*8
Division: =7/8

Arithmetic using cell references: Automatic recalculation

If we simply performed arithmetic using numbers, we may as well use a calculator. Suppose we have a simple situation in which one cell contains an amount of money and another an interest rate. If a third contains the product of the two (a product is the result of multiplying two numbers), this cell will in fact display the interest that would be earned on the capital. This is illustrated in the following screen:

The contents of cell C2 are: =A2*B2

Notice that the formula bar contains the actual contents of a cell. If the cell contains a calculation, the result of the calculation is displayed in the cell.

The = sign indicates that C2 contains a calculation. This calculation involves multiplying the contents of A2 with those of B2.

Now suppose we change the contents of cell B2, without making any change to C2, and press Enter as shown in the following screen:

The value in C3 automatically changes to reflect the changed value in B2. This tool is called automatic recalculation.

When you save a spreadsheet, the formulas are displayed so that they can be reused at a later stage.

Arithmetic operations using the values of cells are written down in the same way as arithmetic operations between values.

Addition: =A2+B2
Subtraction: =A2-B2
Multiplication: =A2*B2
Division: =A2/B2

More complex calculations are carried out using the normal rules of arithmetic.

Suppose cell A2 contains the value 12, B2 the value 6 and C2 the value 8. The following table illustrates how these can be combined into arithmetic formulas.

Cell entry	Equivalent arithmetic expression	Cell display
=A2 + B2 - C2	12 + 6 - 8	10
=A2 + B2*C2	12 + 6*8	60
=(A2+B2)*C2	(12 + 6)*8	144
=A2/B2 - C2	12/6 - 8	-2
=A2/B2 + A2*C2	12/2 + 12*8	98

	Important
	When you carry out arithmetic involving the different operators, remember that different operations have different priorities. The order is as follows: Work out the contents of brackets Carry out the multiplications and divisions Do the additions and subtractions

Recognise and understand error values associated with using formulas

If you enter a formula and Calc is not able to interpret it, it will display an error in the cell. The main errors that you will come across are shown below.

Error message	Cause of error
#NAME	An identifier could not be evaluated. This usually means that the reference to a cell or row or column was invalid. This is also known as error 525
#VALUE	Calc was unable to evaluate one of the cell references in the formula as a number. This error usually occurs when one of the cells referenced in a formula contains text rather than a number. This is also known as error 519.
#REF	This indicates that the formula contains a reference to a cell, row or column that was deleted. It is also known as error 524.
Invalid floating point operation	A formula contains division by zero. A common misunderstanding is that because multiplication by zero results in zero, so does division by zero. Division by zero is however impossible. This is also known as error 503.


Chapter 3. Worksheets		Cell referencing