“It all ends in tears anyway.” [Jack Kerouac]
Abstract
There are several different naïve approaches circulating around which try to round values preserving their rounded sum:
- (worst) Round all values but the last one and replace the last one by the rounded original sum minus the sum of the previously rounded values (i.e. aggregate all rounding errors in the last summand):
| A | B | C | ||
|---|---|---|---|---|
| 1 | Original Data | Aggregate Rounding Error | Formula in C | |
| 2 | Total | 2,594 | 2,59 | =SUM(C4:C8) |
| 3 | ||||
| 4 | 0,875 | 0,88 | =ROUND(B4,2) | |
| 5 | 0,865 | 0,87 | =ROUND(B5,2) | |
| 6 | 0,344 | 0,34 | =ROUND(B6,2) | |
| 7 | 0,455 | 0,46 | =ROUND(B7,2) | |
| 8 | 0,055 | 0,04 | =ROUND(B$2,2)-SUM(C$4:C7) |
- (better, but still bad) Apply a cascading (sliding) round:
| A | B | C | ||
|---|---|---|---|---|
| 1 | Original Data | Cascading Round | Formula in C | |
| 2 | Total | 2,593 | 2,59 | =SUM(C4:C8) |
| 3 | ||||
| 4 | 0,875 | 0,88 | =ROUND(SUM($B$3:$B4),2)-SUM($C$3:$C3) | |
| 5 | 0,865 | 0,86 | =ROUND(SUM($B$3:$B5),2)-SUM($C$3:$C4) | |
| 6 | 0,344 | 0,34 | =ROUND(SUM($B$3:$B6),2)-SUM($C$3:$C5) | |
| 7 | 0,454 | 0,46 | =ROUND(SUM($B$3:$B7),2)-SUM($C$3:$C6) | |
| 8 | 0,055 | 0,05 | =ROUND(SUM($B$3:$B8),2)-SUM($C$3:$C7) |
Let us compare these approaches to RoundToSum.
Calculation Example
[This example is included in the download file at RoundToSum.]
We create 40 random numbers RAND() * 1000 and compare as follows:
Please note that the formula in C3 for the cascading round includes the title row so that it can be copied down.
As you can see, if we simply round each single number, the resulting sum would differ from the original rounded sum by -0.03. Column J (VIII) shows the difference of the aggregated rounding error -0.03 in the last summand. Column F (IV) shows the corresponding rounded numbers. Worst case would be here to come up with an aggregated rounding error of n * 0,005 with n being the count of your numbers. Example: Take 40 times the number 0.005 instead of the 40 random numbers.
The cascading (sliding) round in column I (VII) shows 11 roundings to the wrong side. Column E (III) shows the corresponding rounded numbers. Worst case would be for the cascading round to round half of your numbers to the wrong side when all numbers could have been rounded correctly. Example: Take 20 times the number -0.0049999 and then 20 times the number 0.0049999 instead of the 40 random numbers.
On the other hand, the optimal RoundToSum just rounds 3 values to the wrong side which result in the least number of changes which achieve the correct rounded sum. The worst case would now involve n/2 roundings to the wrong side with n being the count of your numbers. Example: Take 40 times the number 0.005 again instead of the 40 random numbers. This is the best solution with the smallest absolute rounding error for each number and then with the smallest number of roundings to the wrong side.
Conclusion
Use RoundToSum. It will apply the least number of changes and it will result in the correct sum with the smallest absolute (or relative) error. It needs to be applied with an array formula because n > 1 output values depend on n > 1 input values.
A cascading round as shown above, i.e. using in cell E6 the formula =ROUND(SUM($C$5:$C5),2)-SUM($E$4:$E4) does not need any VBA nor does it apply any array formula, but it can leave you with a fairly high number of unnatural roundings which you can hardly explain to any senior manager.
But worst of all is the stupid approach of aggregating all rounding differences in the last summand. Just imagine 1,000 people, each having 49 Cents, adding up to $490, which you should distribute fairly, but rounded to a whole Dollar. In this case you would end up with $490 at the last person, while RoundToSum would give the first 490 persons one Dollar each and all the others zero.
Download
Please read my Disclaimer.
roundtosum.xlsm [58 KB Excel file, open and use at your own risk]