 Subject Guides

# T1: Calculating

Spreadsheets were developed as a tool to store, analyse and manipulate numerical data. They are now commonly used for working with sets of data containing both text and numbers, and for generating graphs and charts. They're also brilliant. Let's play with

Spreadsheets are designed for calculation. In this section we'll look at how to go about performing basic arithmetic in a spreadsheet using simple formulae. We'll see ways in which we can quickly replicate formulae, and how we can employ basic statistical functions that simplify some of the maths we might want to carry out.

## Performing calculations

You can perform calculations using:

• A range of inbuilt functions that just need to know which values to use.

Formulae are entered directly into the cell where you want the answer to appear, and always start with an equals sign (=).

Here's some important points about using formulae:

• Avoid 'hard-entering' numerical values in a formula. It's better to reference values held in other cells in your spreadsheet where possible.
• If a value can be calculated from other values in your spreadsheet, use a formula. Never type an ‘answer’ value directly into a cell, no-matter how easy the calculation is — use a formula and that way it will update if the input values change.
• As with data entry, always press Enter or Tab after entering a formula.

## Basic arithmetic

Formulae can contain basic arithmetic. The following symbols are used for basic arithmetic:

+ - * / ^

Here is a formula that subtracts two cell values. The result is shown in cell A3, but you will also see the formula in the formula bar: =A1–A2

A B fx =A1-A2 9 6 3 When entering a formula, a cell reference can be typed in directly, entered by clicking on that cell, or selected with the cursor keys.

### Order of operations

When a formula involves more than two values, spreadsheets use the standard rules of operator precedence: powers and square roots first, then multiplication and division, then addition and subtraction. If you are unsure in what order a calculation will be performed, avoid ambiguity by using brackets. Here's a couple of examples:

Adding up items sold in 2000 and 2001, then multiplying by the unit cost:

A B C D fx =(A2+B2)*C2 2000 2001 Cost each Total 18 29 £ 10.00 £ 410.00

#### Multiply first

Calculating the cost of the number of items ordered and then adding postage & packaging:

A B C D fx =(A2*B2)+C2 Items ordered Cost each P&P cost Total 18 £ 10.00 £ 5.00 £ 185.00

### Unexpected results

If the result of your formula can't be presented, an error message may be given. Here's some common examples:  Explanation Solution
Pop-up: "The formula you entered contains an error" Cell contains #N/A Formula not understood Check for missing arguments, brackets, etc.
Cell contains ####### (not applicable to Google Sheets) Result calculated, but cell too narrow to display value Adjust column width or format to accommodate result
Cell contains #REF! Cell contains #REF! There are cell references in the formula that can't be found (often this is because a column or row has been deleted; in Google Sheets it may also be because a permission hasn't been granted) Check that the cell references in the formula are correct
Pop-up: "Circular Reference Warning" Cell contains #REF! Formula uses its own cell in the calculation Check that the cell references in the formula are correct
Cell contains #DIV/0! Cell contains #DIV/0! Formula is trying to divide by 0 (which isn't allowed). Particularly common when an average function has no values to work withCheck formula cell references are correct; check data is correct; check that any functions have sufficient values)

## Replicating formulae

Often a spreadsheet formula will need to be repeated – for example to find the total of several columns of numbers. You can use copy and paste, or the fill handle.

When you do either of these, cell references in the new formulae are updated relative to the movement (they are relative references).

Take this example:

A B C fx =A1 Here I am! Yoohoo! Hello there! Oi! Ey up! Now I'm here! Here I am! Over here! Ahoy! Wotcha! How do!

The formula in cell D3 is simply pointing at cell A1, which is three cells to the left and two cells up from the D3.

If we were to copy cell D3 and paste its contents in cell E5...

A B C D fx =B3 Here I am! Yoohoo! Hello there! Oi! Ey up! Now I'm here! Here I am! Over here! Ahoy! Wotcha! How do! Now I'm here!

...the newly pasted formula is still pointing at a cell that's three cells to the left and two cells up from itself, but relative to its new location in E5, so it's now pointing at B3. Notice how the formula in the formula bar has changed to account for the formula's new location.

Following this same principle, when dragging with the handle...

• dragging to the right (or left) will update the column letters accordingly
• dragging down (or up) a column changes the row numbers accordingly.

A B C fx 10 15 100 20 30 200 =A1+A2 =B1+B2 =C1+C2  ### Absolute references

In some situations you will not want the references to change as you copy your formula. That's where absolute references come in!

Consider this example: the total in row 6 must always be divided by the maximum possible score (cell B1) to give the percentage score (row 7). Our starting formulae are entered into cells B6 (for the total) and B7 (for the percentage), and are then dragged across the rows:

A B C D E fx Max score 205 Mike Steph Sam Test 1 83 76 95 Test 2 67 72 63 Total score =B4+B5 =C4+C5 =D4+D5 ← Both references need to change relatively Total score =B6/\$B\$1 =C6/\$B\$1 =D6/\$B\$1 ← Always needs to reference B1

In row 6, the formula in B6 updates when replicated into columns C and D.

However, in row 7 a relative reference to the cell B1 would change to C1, then D1, giving an incorrect result.

To prevent this, dollar signs (\$) are used to define an absolute reference – a reference that should not be changed when the formula is replicated.

By entering =B6/\$B\$1 in B7, the formula can be replicated correctly – the dollars mean that the cell reference is locked to B1.

### 'Mixed' references

The dollars in absolute references 'lock' the 'coordinate' they immediately precede. So in the case of \$B\$1, the dollar in front of the B locks the reference to column B, and the dollar in front of the 1 locks the reference to row 1.

There are circumstances where you'd only need one of these 'coordinates' (the column or the row, but not both) locking, for instance:

A B C D E F fx Name Monthly bonus Jan Jan + bonus Feb Feb + bonus Arielle £125 £500 =C2+\$B2 £600 =E2+\$B2 Phil £75 £600 £550 Siobhan £100 £550 £400

In this example, the bonus in column B is added to each month’s figure. So in cell D2 we could have C2+B2. This formula would replicate down the column OK, but cannot be copied to F2 (for February) as this would give E2+D2. We need to ‘lock’ the use of the B column, without affecting the ability of the row to change.

The absolute reference must be applied only to the column, so we place the dollar before the part of the reference we want to ‘fix’:

=C2+\$B2 When entering references needing absolute or ‘mixed’ references, you can enter the reference as usual and then press the F4 key to cycle through the various ‘dollar’ options.

### Referencing between worksheets

Each cell in a spreadsheet is unique by virtue of the column, the row and the sheet on which it resides. This means you can reference cells on one sheet from another.

Sheet names are combined with cell references using single quotes (') around the sheet name, and an exclamation mark (!) between the sheet name and the cell reference:

'Sheet name'!CellReference

e.g.:

'Form Responses 1'!B2 The quotation marks (') around the sheet name are only required if your sheet name has a space character ( ) in it.
If you rename your sheet at a later date, your sheet references will update accordingly.

You can usually employ a ‘point and click’ or ‘drag over’ method to enter references from another sheet, but you need to keep an eye on the content of the formula bar. There's a couple of things to watch for:

• If you switch sheets before you have begun entering the next item in the formula, you may find the sheet name changes again, so enter the next item first;
• When you’ve finished a formula, but are not on the sheet on which it’s being entered, resist the urge to switch sheets – instead simply Enter the formula and it will take you back itself.

## Common statistical functions

Spreadsheets have hundreds of functions used to perform calculations beyond simple arithmetic. Excel and Google use the same set of core functions, though a few are specific to each tool. Some common functions can be found under a Σ "Functions" icon on the icon ribbon, and at: Home > Editing > AutoSum Insert > Function

...namely:

SUM Add together two or more cells Find the mean average of two or more cells Count the number of cells containing numbers in two or more cells Find the highest value in two or more cells Find the lowest value in two or more cells

Typically these functions work with ranges of cells. For instance...

=SUM(A2:F2)

...would find the sum total of all the cells in row 2 of a sheet, from column A to column F inclusive:

A B C D E F G fx =SUM(A2:F2) Mon Tue Wed Thu Fri Sat Total 6 5 7 3 4 2 27

Note that:

• Each function has a particular name (in this case SUM
• Every function must be followed by a pair of brackets ( )
• The brackets will usually contain one or more arguments — extra information needed by the function — separated by commas (,) where more than one piece of information is required As soon as you open the brackets of a function, the spreadsheet will offer up a tool-tip summary of what information that function needs. Google Sheets summaries can be expanded and contracted with the ?, X and arrow toggles, to get different amounts of contextual help.

### Advanced ranges Many functions need to reference a range of cells, which can be one cell, several cells in a line, one or more columns/rows or a rectangular region.

A fx

Select the example notations below to highlight the selected ranges in the spreadsheet (try to guess what they'll be before clicking):

B2
A2:F2
D2:D7
E:E
5:5
B:F
3:8
B2:E5
A1:B5,D7:G9
A2:A
C3:3

See a summary table of the above ranges...

Range typeExample notation
single cellB2
several adjacent cells within a rowA2:F2
several adjacent cells within a columnD2:D7
one columnE:E
one row5:5
rectangular areaB2:E5
multiple regionsA1:B5,D7:G9
Partial (open-ended) column (Google Sheets only)A2:A
Partial (open-ended) row (Google Sheets only)C3:3

### Entering functions

Excel includes a dialogue tool to help when entering functions, but learning function syntax is an important part of spreadsheet use. Cell references can be typed in, but shortcut methods can save time:

• As with simple formulae, you can enter a cell reference simply by pointing and clicking on the cell.
• A range of cells can be entered by dragging over them.
• The reference for an entire column/row can be entered by clicking on the column/row label.

In addition to the "Σ" common functions menu mentioned above, you can find all of the functions organised by type at: Formulas > Function Library Insert > Function You don't have to pick functions from a menu. You can just type them into your formula.
Typing "=" into a cell followed by a letter will bring up a list of functions for that letter.

There are a number of functions with similar names that perform similar, but subtly different, functions. For instance, there are several functions that count a number of values:

COUNT counts the number of numerical values in a range counts the number of all types of value in a range counts the number of blank cells in a range counts the number of unique values in a range (Google Sheets only)  Google Sheets versions of the files are listed below. In each case you will be prompted to make your own copy of the spreadsheet files, for which you'll need to be signed into a Google account. 