I am a supporter ofSt. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation toSt. Joseph's, please. Information onSt. Joseph's

"All some folks want is their fair share and yours." [Arnold H. Glasow]

You need to present percentages of some numbers but they need to sum up to 100% EXACTLY?

Example: You have 115, 222 and 333 but their percentages (rounded to 2 decimals) 17.16%, 33.13% and 49.70% add up to 99.99%, not 100%.

This is how it works - see two examples: one for absolute values, the other for percentages (relative values). This method minimizes the absolute error if you need to amend a value:

Please notice that this method is a derivation of the Hare-Niemeyer method. A practical implementation of this method you can see at this allocation of overhead costs.

Other, but similar methods are shown at D’Hondt Method and at sbExactRandHistogrm.

If you like to know more about the distribution of sums of rounded percentages, see Mosteller, Youtz and Zahn: "The Distributions of Sums of Rounded Percentages", Demography (1967), 4, p. 850 - 858, or Diaconis and Freedman: "On Rounding Percentages", Journal of the American Statistical Association, Vol. 74, No. 366. (Jun., 1979), pp. 359-364.

You can download a 50 KB Excel 2013 © sample file here but please notice my disclaimer.

Name

sbRoundToSum - Calculate rounded summands which exactly add up to the rounded sum of unrounded summands

Synopsis

sbRoundToSum(vInput, [lDigits], [bAbsolute], [bDontAmend])

Description

sbRoundToSum calculates rounded summands which exactly add up to the rounded sum of the unrounded summands.
It uses the largest remainder method which minimizes the absolute error to the original unrounded summands.
This function needs to be entered as an array formula into the cells for the rounded summands.

Example

If you need to distribute 1 EUR to three people, all of them should get 1/3 EUR but you need to amend this to whole cents:

Options

vInput
Range or array which contains unrounded summands

lDigits

Optional - standard value is 2 if not provided
Number of digits to round to. For example: 0 rounds to integers, 2 rounds to the cent, -3 will use thousands

bAbsolute
Optional - standard value is True if not provided
True - takes the summands as they are
False - works on the summands' percentages to make all percentages add up to 100% exactly

bDontAmend
Optional - standard value is False if not provided
True - do not amend the rounded summands to match the rounded sum. This parameter is mainly for ease of use or
presentation to see this function's impact
False - perform amendments as described above