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.
You can perform calculations using:
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:
Formulae can contain basic arithmetic. The following symbols are used for basic arithmetic:
|Add||Subtract||Multiply||Divide||to the power of|
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
When entering a formula, a cell reference can be typed in directly, entered by clicking on that cell, or selected with the cursor keys.
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:
|2||18||29||£ 10.00||£ 410.00|
Calculating the cost of the number of items ordered and then adding postage & packaging:
|1||Items ordered||Cost each||P&P cost||Total|
|2||18||£ 10.00||£ 5.00||£ 185.00|
If the result of your formula can't be presented, an error message may be given. Here's some common examples:
|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 with||Check formula cell references are correct; check data is correct; check that any functions have sufficient values)|
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:
|1||Here I am!||Yoohoo!|
|3||Ey up!||Now I'm here!||Here I am!|
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...
|1||Here I am!||Yoohoo!|
|3||Ey up!||Now I'm here!||Here I am!|
|5||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...
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:
|6||Total score||=B4+B5||=C4+C5||=D4+D5||← Both references need to change relatively|
|7||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.
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:
|1||Name||Monthly bonus||Jan||Jan + bonus||Feb||Feb + bonus|
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’:
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.
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:
'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:
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
|SUM||Add together two or more cells|
|AVERAGE||Find the mean average of two or more cells|
|COUNT||Count the number of cells containing numbers in two or more cells|
|MAX||Find the highest value in two or more cells|
|MIN||Find the lowest value in two or more cells|
Typically these functions work with ranges of cells. For instance...
...would find the sum total of all the cells in row 2 of a sheet, from column A to column F inclusive:
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.
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.
|Range type||Example notation|
|several adjacent cells within a row||A2:F2|
|several adjacent cells within a column||D2:D7|
|several adjacent columns||B:F|
|several adjacent rows||3:8|
|Partial (open-ended) column (Google Sheets only)||A2:A|
|Partial (open-ended) row (Google Sheets only)||C3:3|
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:
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|
|COUNTA||counts the number of all types of value in a range|
|COUNTBLANK||counts the number of blank cells in a range|
|COUNTUNIQUE||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.