On Nov 20, 10:46*am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
If you always want the items to add to a single fixed value,
your last calc needs to be
=FixedValue - SUM(Other rounded Values)

I made the same mistaken suggestion yesterday in another thread, and
I've been guilty of doing this in spreadsheet. But then I realized
that it is wrong, or at least misleading.

For example, consider the numbers 50.4%, 48.4%, 0.4%, 0.4%, 0.4%. If
we round the first 4 and compute the 5th by 1-sum(first 4), we will
get 50%, 48%, 0%, 0%, 2%. Obviously the 5th percentage is a
misrepresentation. The problem is: all of the rounding "error" is
accumulated into the last formula.

Moreover, if the sum of N rounded percentages might exceed 100%, so
can the sum of N-1 rounded percentages. So 1-sum(other rounded
values) might go negative. Example: 50.5%, 48.9%, 0.5%, 0.1%. The
first 3 become 51%, 49% and 1%, which sum to 101%. So the 4th,
evaluated as 1-sum(...), becomes -1%.

Of course, that can be avoided by max(0,1-sum(other rounded values)),
or rounding down all other percentages.

But we are still stuck with the first problem, namely: accumulating
all of the rouding "error" into the last formula.

Aside: I wonder if using banker's rounding would always make 1-sum
(...) work insofar as avoiding negative results. But even banker's
rounding suffers from the accumulation flaw. Consider my second
example. I believe that with banker's rounding, the first 3 become
50%, 49%, 0%, which sums to 99%. But the 4th becomes 1%, which is a
misrepresentation.