“Each mistake will find a fool making it."
There are several different naive 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).
- (better, but still bad) Apply a sliding round (see below).
Let us compare these approaches with RoundToSum.
[This example is included in the download file at RoundToSum.]
We create 40 random numbers (RAND()*1000) and compare as follows:
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.
The sliding round in column I (VII) shows 11 roundings to the wrong side. Column E (III) shows the corresponding rounded numbers.
On the other hand, RoundToSum just rounds 3 values to the wrong side which result in the least number of changes which achieve the correct rounded sum.
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 sliding 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 rounding which you can hardly explain to any senior manager.
But worst of all is the stupid approach of aggregating all rounding differences to the original rounded sum 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.