How to Get the Sum of Rounded Numbers “correct”?
It’s quite common that the calculation adds precise numbers which are formatted to be less precise. It might also happen that due to rounding, the formatted result seems mathematically incorrect on screen.
Example:
| Cell value | Cell value formatted with 1 decimal | |
| A | 12,75 | 12,8 |
| B | 12,75 | 12,8 |
| SUM A+B | 25,5 | 25,5 |
The last red number seems incorrect, because 12,8 + 12,8 = 25,6
Usually this is OK, as from the source data point of view, this is mathematically correct. But on some rare cases you want to add up numbers as they are displayed and you want the result to be 25,6. To let Excel do the math at the displayed precision level go to:
On Excel 2007: Excel Options > Advanced, scroll down to When calculating this workbook
On Excel Mac 2008: Excel -> Preferences -> Calculation
And check the field ‘Set precision as displayed‘
This should be used with caution as Excel changes the numbers in cells permanently.
