Why doesn't my total match the sum of the rounded numbers?
Formula (Excel and Google Sheets)
=ROUND(B2*$F$1,2)The cells show rounded values but store every decimal. Round each line with ROUND: =ROUND(B2*$F$1,2), and the total adds what you see.
- Result on the example
- one result per row in
C2, filled down toC6 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in C2, filled down to C6. Change any input and the result updates, or edit the formula to experiment.
How it works
Unrounded, each tax amount is stored with all its decimals (1.071675) but displayed in cents (1.07). The total adds the stored values, 4.59525, and displays 4.60, while the lines you see add up to 4.59. ROUND makes each stored value match what is shown, so the lines and the total agree.
=ROUND(B2*$F$1,2)Result1.07
B2$12.99The value in B2.
$F$18.25%The value in F1.
B2*$F$11.071675The * operator multiplies.
ROUND(B2*$F$1,2)1.07ROUND: Rounds to a number of digits; negative digits round left of the decimal.- number
B2*$F$11.071675 - num_digits
2
- number
In Google Sheets
The same formula works in Google Sheets: =ROUND(B2*$F$1,2). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =B2*$F$1 | $1.07, $0.70, $1.96…Different result | The unrounded tax. Keeps every decimal, so the lines shown add up to 4.59 while the total shows 4.60. |
Common mistakes
- A number format changes only what you see, never the value. Only ROUND changes it.
- Round where the money is fixed, usually each line. Rounding only the total still leaves lines that don't add up to it.
- Avoid the option "Set precision as displayed": it rounds every value in the workbook permanently.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Why doesn't my total match the sum of the rounded numbers
C2, calculate the tax at the rate in F1, rounded to cents, so that the total in C7 matches the lines. Fill down to C6.Questions
Should I round the total too?
When the lines are rounded, their sum is already in whole cents. Round the total only if it includes other calculations.
What is the difference between ROUND and a number format?
A format changes how a value looks; ROUND changes the value other formulas use.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.