“Every fool can know. The point is to understand.” [Albert Einstein]

Rounding Values Preserving Their Sum

Rounded values do not always sum up to their original total, as demonstrated in this article. How can you ensure that the sum of rounded percentages equals exactly 100%? Is it possible to guarantee that, for accounting purposes, the distribution of overhead costs precisely matches the original total? These challenges are well-known and have been studied extensively.

This article introduces a simple solution using Excel/VBA. The function presented here can round relative values (e.g., percentages) to ensure they sum to exactly 100%. It can also round absolute values (such as cost distributions) while preserving their original sum after rounding. A key parameter allows users to choose which type of error to minimize — absolute error or relative error — compared to the common half-up rounding method.

Please read my Disclaimer.

Plumhoff_Rounding_Values_Preserving_Their_Sum_with_RoundToSum.pdf [1,247 KB PDF file, open and use at your own risk]