ROUND inside a calculation
Round each line where it is calculated, so the invoice adds up to the penny.
Task:
Each invoice line is quantity × unit price, plus 20% VAT. Put the VAT on each line in D2:D5, rounded to 2 decimal places in the same formula, and the invoice's total VAT in D6.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of ROUND
0/4The same formula in the other shapes it takes at work.
- ROUND Function
Round numbers to specific decimal places.
CoreBeginner - ROUND to the nearest thousand
A negative number of digits rounds to the left of the decimal point.
Beginner - ROUNDUP and ROUNDDOWN: rounding in one direction
When the rule is "always up" or "always down", ROUND is the wrong tool.
Intermediate - ROUND inside a calculation
Round each line where it is calculated, so the invoice adds up to the penny.
Intermediate
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Qty | Unit price | VAT |
| 2 | Cable ties | 7 | 3.15 | |
| 3 | Junction box | 3 | 12.49 | |
| 4 | Conduit (m) | 13 | 1.87 | |
| 5 | Fuse | 11 | 0.83 | |
| 6 | Total VAT |
What this exercise teachesMay contain the answer
An invoice prints each line to the penny, so the total has to be the sum of the printed lines. If you only format the lines to 2 places, the total adds the unrounded values and can disagree with the column above it by a penny. Rounding inside the line formula makes what is shown and what is added the same number.