Neia said:
When I use this format I have a problem:
the percent values do not sum correctly.
21%, 25%, 55% and it sums 101%.
What can I do?
It might not be worth it to do anything. Look at almost any financial that
shows percentages of categories, and you will probably find a footnote to the
effect that numbers are rounded and might not add up to the total.
It is called a quantization error, in your case due to rounding to integers.
Suppose you have 4 $1 bills and you want to divide them among 3 people.
Ostensibly, that would 3/4 of a $1 bill per person; but of course, you cannot
tear up the $1 bills (and glue some pieces together). "What can you do?".
There are various strategies for distributing quantization errors. But
there are always circumstances where each strategy fails. The simplest (and
poorest) approach is to compute all but the last percentage by a formula of
the form =ROUND(A1/$A$4,2), and the last percentage by the formula
=1-SUM(A1:A2).
(Note that rounding to 2 decimal places rounds to an integral percentage.)
In your particular example, the simplest approach might be to display the
percentages with greater precision, for example Percentage with 1 or 2
decimal places. There is no guarantee that they will sum to 100%; but there
is a better chance that they will. Ironically, Percentage with 1 decimal
place works better than 2 decimal places for your particular example.
Or you can just accept the fact that quantization errors happen.
----- original message -----
