Should always use round function when using decimal numbers?
I would say NO.
Granted, digital calculations often lead to rounding errors. Alone they are usually insignificant, but when they accumulate it can cause significant error.
Deliberate rounding is good. You should do it whenever necessary. For that, you need to understand when it is necessary.
You should not make a habit to always round. The “precision as shown” setting is also often a bad idea (lazy).
Use rounding
- on calculations which signify a “transaction”.
common contexts:
-
money changing hands
=ROUND(X;2) rounds to the penny
-
Worktime/overtime calculation on flexible work schedules
=MROUND(x;1/1440) rounds to the minute.
=MROUND(x;1/86400) rounds to the second.
- When comparing numbers for equality
Decide what precision (number of decimals) you need, and round both numbers to compare (or the calculated difference) to that precision.
Avoid rounding
- when dividing magnitudes toward significantly smaller magnitudes. Multiple step calculations, complex models.
common context:
Buying large packs of goods (raw materials, consumables) to be shared by multiple units, calculating cost per unit/department/whatever as consumed, accumulating unit cost to determine total cost.
Background
The rounding operation does not remove the rounding errors which are inherent in the spreadsheet number representation. It just makes the error more predictable.This helps in two ways:
- Comparing two seemingly equal numbers, the compare will return “equal” because the rounded versions will both have the same rounding error.
- When accumulating numbers (running sum), sensible rounding will “strip” accumulated errors.
For basic ledgers, where every number is written with two decimals and every math operation is an add or subtract, the “precision as shown” may be safe. Whenever you introduce different formats or other arithmetic operations, precision is easily lost.
I had a colleague who asked me for help with his timesheet. He told me he had used it for years, but after a software update he lost a minute. Undoing the update didn’t help. It turned out that after five and a half years of daily rounding errors accumulating, the sum of errors amounted to more than 30 seconds, which the format rounded to the minute for displaying so he lost a minute. A ROUND() on the monthly sum formula resolved the issue. The update had nothing to do with the issue.
