Excel track · Exercise 30 of 86
ROUND: money to two decimals
Round a marked-up total once, and know that the stored value changed.
Intermediate
Cost sheet with markup
Read-only data · answer in B6
In B6, total the three costs, add 8% markup, and round the result to 2 decimals.
| A | B | |
|---|---|---|
| 1 | Item | Cost |
| 2 | Hosting | 1500.24 |
| 3 | Support | 1120.5 |
| 4 | Design | 1500 |
| 5 | ||
| 6 | Total with 8% markup |
Reference answer
What it does
ROUND takes a number and a count of decimals to keep. The three costs total 4120.74, and an 8% markup multiplies that by 1.08, which gives 4450.3992. Wrapping the whole calculation, =ROUND(SUM(B2:B4)*1.08,2), stores 4450.4.
ROUND changes the number itself. Showing 4450.3992 with two decimals leaves every digit in the cell, so the next column still adds 4450.3992. The stored 4450.4 is what the invoice carries, and 4450.40 on screen is that value with a format applied.
Put ROUND around the finished calculation. Rounding each cost after markup and then adding can land on a different figure from rounding the total, and this formula rounds once, at the end. A negative count rounds left of the point: -2 turns 4450.3992 into 4500.
Syntax
=ROUND(number, num_digits)Worked example
On Cost sheet with markup, =ROUND(SUM(B2:B4)*1.08,2) returns 4450.4.
=ROUND(SUM(B2:B4)*1.08,2)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | The second argument is text, such as "2" in quotes after a paste from another system. |
#NAME? | The name is typed ROUNDOF or ROUND_UP, and Excel recognises only ROUND. |
Rounded twice | Rounding each cost after the markup and then adding gives a different sum from rounding the total once. |
At work
Quotes and invoices, where the number on the page has to match the number the cell stores.