Add up 600 numbers by hand?
A support team logs how many minutes each fault takes to fix. There are 600 faults. The manager asks for the total, the longest fix and the average.
A spreadsheet answers all three with one short formula each.
By the end of this lesson
Note
I can use arithmetic, SUM, MIN, MAX, AVERAGE, IF and COUNTIF to answer a question about data.
Key words
| Word | What it means | Example |
|---|---|---|
| Formula | a calculation typed into a cell, starting with = | |
| Function | a ready-made calculation with a name | SUM |
| Range | a block of cells, written from the first to the last | B2:B7 |
| Cell reference | the name of one cell: its column and row | B2 |
| Condition | a test that is true or false | |
| Average | the total divided by how many values there are |
The cells students fill in are left blank in this preview.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Arithmetic in a cell
A formula starts with an equals sign. Use + to add, - to take away, * to multiply and / to divide.
Cells have names, called cell references. The cell in column B, row 2 is B2. A formula can use them, so it changes when the numbers change.
Six functions to know
| Function | What it does | Example formula | Answer for 3, 7, 5 |
|---|---|---|---|
| SUM | Adds a range | `=SUM(B2:B4)` | 15 |
| MIN | Smallest value | `=MIN(B2:B4)` | 3 |
| MAX | Largest value | `=MAX(B2:B4)` | 7 |
| AVERAGE | Total divided by count | `=AVERAGE(B2:B4)` | 5 |
| COUNTIF | Counts cells that match | `=COUNTIF(B2:B4,7)` | 1 |
| IF | Chooses between two answers | `=IF(B2>=5,"Long","Short")` | Short for the first cell, 3 |
The cells students fill in are left blank in this preview.
Worked example: average fix time
| Step | Working | Result |
|---|---|---|
| Add the minutes | 3 + 7 + 5 | 15 |
| Count the faults | There are 3 values | 3 |
| Divide | 15 divided by 3 | 5 |
The cells students fill in are left blank in this preview.
Check
Have a go
Check
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.