"Mojo" wrote:
This might be an age old problem
[....]
sometimes certain values are 1% less or more than
they should be and as you add up the displayed
figures the total might come out at 99% or 101%

This is a very common real-world effect of rounding. There are no good ways
to avoid it completely, although there are schemes for ameliorating it.
Professional accounting reports usually have a footnote acknowledging the
fact that rounded values might not add up to the whole.


My code for each percentage value is simply:
Round((intPeopleCount / intTotalPeopleCount) * 100, 0) & "%"

It is unclear if this is VBA code and you are storing this result into
Range.Value, or if this is a formula. If the latter, it would be better to
store a number and use the Percentage format; that is, simply
Round(intPeopleCount/intTotalPeopleCount,2).


I really want to make this work, but it seems fraught with issues!!
Any ideas?

Assuming you store numbers formatted as Percentage, one approach is to put
the following formula into each cell (assuming A2:A9 is intPeopleCount, A10
is intTotalPeopleCount and these formulas are in B2:B9):

=max(0,round(sum($A$2:A2)/$A$10 - sum($B$1:B1),2))

This has the effect of distributing the round-off "error". Note that the
intent is for the relative references A2 and B1 to change in each formula.
And this assumes that B1 is empty, text or zero.

That works with your example, and many others. However, I don't believe it
is a panacea. There may be examples where it still does not work, or it
produces nonsensical results (e.g. non-zero percentage for a category of
zero).

PS: I threw in MAX(0,...) as an insurance policy. I am not sure it is
needed.


----- original message -----

"Mojo" wrote in message
...
Hi All

This might be an age old problem, but I just wanted to see if I wasn't
missing something obvious in my code.

Basically I have say 31 people (this figure can range from 10 to 100) who
are put into 8 categories. Once they have been categorised I simply need
to
show how many are in each category as a number and as a percentage. Now
the
number aspect of display is easy, but I'm coming unstuck with the
percetange
aspect.

In essence as I do my % calc for each category sometimes certain values
are
1% less or more than they should be and as you add up the displayed
figures
the total might come out at 99% or 101%, which looks daft.

An example of a display problem is as follows

1 0 2 9 11 5 0 2 = 30
people
3% 0% 7% 30% 37% 17% 0% 7% = 101% !!!!

My code for each percentage value is simply:

Round((intPeopleCount / intTotalPeopleCount) * 100, 0) & "%"

I really want to make this work, but it seems fraught with issues!!

Any ideas?

Thanks