Learning Excel: ROUND
Rounding
Ruth puts the average in the newsletter: 18.44 kilos of tomatoes. A member checks the maths and gets 18.4375. "So who's right?" Both of them, and it all comes down to the difference between showing a number and rounding it.
Showing is not rounding
D12 holds the average of the tomatoes. The cell shows 18.44, because the column is formatted with two decimal places. But what's in the cell is 18.4375.
Formatting only changes what you see. If another formula calculates with D12, it calculates with 18.4375.
=AVERAGE(D2:D11)
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Plot | Tenant | m² | Tomatoes (kg) | Courgettes (kg) | Beans (kg) |
| 2 | P01 | Molly | 50 | 18.40 | 9.20 | 4.50 |
| 3 | P02 | Karim | 25 | 7.90 | 2.10 | |
| 4 | P03 | Annabel | 50 | 21.60 | 12.80 | 6.30 |
| 5 | P04 | 25 | ||||
| 6 | P05 | Will | 75 | 30.20 | 15.50 | 8.80 |
| 7 | P06 | Sophie | 25 | 6.50 | 4.10 | |
| 8 | P07 | Ben | 50 | 15.70 | 10.30 | 5.20 |
| 9 | P08 | Fatima | 75 | 27.90 | 18.60 | 9.40 |
| 10 | P09 | Luke | 25 | 3.80 | 1.90 | |
| 11 | P10 | Jodie | 50 | 19.30 | 11.10 | 5.70 |
| 12 | Average | 18.44 | ||||
| 13 | Highest | |||||
| 14 |
The ROUND function
If you really want a rounded number, use ROUND. The first argument is the number, the second the number of decimal places. =ROUND(D12,1) turns it into 18.4, and that's what everything after it calculates with.
A function can also sit inside another function: =ROUND(AVERAGE(D2:D11),1) works out the average first and then rounds it.
=ROUND(D12,1)
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Plot | Tenant | m² | Tomatoes (kg) | Courgettes (kg) | Beans (kg) | |||
| 2 | P01 | Molly | 50 | 18.40 | 9.20 | 4.50 | 18.40 | ||
| 3 | P02 | Karim | 25 | 7.90 | 2.10 | ||||
| 4 | P03 | Annabel | 50 | 21.60 | 12.80 | 6.30 | |||
| 5 | P04 | 25 | |||||||
| 6 | P05 | Will | 75 | 30.20 | 15.50 | 8.80 | |||
| 7 | P06 | Sophie | 25 | 6.50 | 4.10 | ||||
| 8 | P07 | Ben | 50 | 15.70 | 10.30 | 5.20 | |||
| 9 | P08 | Fatima | 75 | 27.90 | 18.60 | 9.40 | |||
| 10 | P09 | Luke | 25 | 3.80 | 1.90 | ||||
| 11 | P10 | Jodie | 50 | 19.30 | 11.10 | 5.70 | |||
| 12 | Average | 18.44 | |||||||
| 13 | Highest | ||||||||
| 14 |
Try it yourself
In I2, work out the average kilos of beans, rounded to one decimal place straight away.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Plot | Tenant | m² | Tomatoes (kg) | Courgettes (kg) | Beans (kg) | ||||
| 2 | P01 | Molly | 50 | 18.40 | 9.20 | 4.50 | ||||
| 3 | P02 | Karim | 25 | 7.90 | 2.10 | |||||
| 4 | P03 | Annabel | 50 | 21.60 | 12.80 | 6.30 | ||||
| 5 | P04 | 25 | ||||||||
| 6 | P05 | Will | 75 | 30.20 | 15.50 | 8.80 | ||||
| 7 | P06 | Sophie | 25 | 6.50 | 4.10 | |||||
| 8 | P07 | Ben | 50 | 15.70 | 10.30 | 5.20 | ||||
| 9 | P08 | Fatima | 75 | 27.90 | 18.60 | 9.40 | ||||
| 10 | P09 | Luke | 25 | 3.80 | 1.90 | |||||
| 11 | P10 | Jodie | 50 | 19.30 | 11.10 | 5.70 | ||||
| 12 | Average | |||||||||
| 13 | Highest | |||||||||
| 14 |
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson