Wrong sum value after calculate with Percentage

[Neon520]

Hi everyone,

I have a problem with percentage calculation here.
I need to calculate the numbers of 5 different items base on the Total of
all 5 items, so what I did is using the amount for each item, let's say
item#1 in A1 divide by the Total amount of items in A6, I know that this will
give me the percentage of item#1, and I do the same for the rest of the
items. If let's say there is a Total amount of Hours that put into making
these items I need to distribute this total hour according to the percentage
that I had calculated, I would use the total hour to multiply by the
percentage for each item number, right?

But the problem that I'm having is when I try to verify the total hour by
sum of the hour for each item back manually; some time it's short by 1 and
sometime it's over by 1.
I didn't notice this at first when I do =sum(B1:B5), but when I pick up the
number manually instead, I notice that it's off by 1 number sometime.

What can I do to prevent this from happening? HELP!!!

 

[Niek Otten]

If you format A1 as General and widen the column, you'll probably discover
that there are decimals involved that you didn't see first.

 

[Neon520]

Yes, I do realize that. It's just a matter of formatting the cell, but how
can I limit the percentage so that it will calculate correctly when I verify
it manually back.

I just want to get it right every time without having to worry what
percentage it is or what the hours it is.

Thanks for you fast reply.

Neon520

 

[Niek Otten]

use something like

=ROUND(A1,2)/ROUND(A6,2)

if you use 2 decimals.

An alternative is Tools>Options>Calculation tab, check Precision as
displayed.
But then it happens for all your data, so think about wheter that is what
you require.

 

[Neon520]

Hi Niek,

I was trying the Round function that you suggested, and hoping that it will
help, but unfortunately it doesn't, because after all when using Round
function it will round up the way we use Cell Formatting. So it still end up
short by 1 or over by 1.

Any other ideas?

Thank you,
Neon520

 

[David Biddulph]

I don't think we understand what you are trying to achieve.

If you want an exact answer (within the 15 digit precision of Excel's
calculations), then don't round. If you want to round, then use the ROUND
function. If you don't want either of those options, perhaps you could
explain clearly whay you *do* want?

Perhaps you can give an example of your problem?

 

[joeu2004]

Yes, I do realize that. It's just a matter of formatting the cell,
but how can I limit the percentage so that it will calculate
correctly when I verify it manually back.

First, it is well-known that when percentages are calculated and
presented for every item, the manual sum of the presented percentages
will be more or less than 100%. There is often an explicit footnote
to that effect in financial reports, etc.

So it is not clear that it is worth any bother to correct for this.
It is an accepted anomaly.

That said, you can mitigate the problem by changing the last
"percentage" formula.

Suppose the formulas in B1:B4 are of the form =A1/$A$6, where A6 is
the total of A1:A5. Then ostensibly, B5 should have the formula =1-sum
(B1:B4).

However, that paradigm need some tweaking to completely(?) remove the
anomaly.

First, for B1:B4, make the formula =round(A1/$A$6,4) if you want the
result to be of the form xx.xx%.

Second, for B5, make the formula =round(1-sum(B1:B4),4).

The "extra" rounding in B5 ensures that WYSIWYG. For example, if 1 -
sum(B1:B4) displays as 12.34, it might not compare "equal" to 12.34
entered manually (typed) in another cell. But round(1-sum(B1:B4),4)
will have exactly the same internal representation as if you entered
12.34 manually (typing).

HTH.

PS: Caveat: of course, if any formula that attempts to "recover" the
original number by applying the rounded percentage to the total, it
might not equal the original number. You really cannot have it both
ways -- unless you retain and use the "exact" percentage (with the
limits of the internal representation) in a helper cell.


----- original posting -----

 

[Neon520]

Hi David,

Sorry for the unclear message.
Here is what I need to get done.

Eventually, I need to have in 2 digit decimals.
Total Hour: 16
Let's say here is the percentage, (keep in mind that these percentage will
change base on something else)

Percentage Result
47% 7.55
31% 4.96
12% 1.99
3% 0.54
6% 0.97

The number in the result column should add up to "16"- the original number,
but it does not, it come up with 16.01.

Now that I look at it carefully, I notice that the percentages don't even
add up to 100, it' 99. Why?
I achieve the percentage by dividing a set of number by one big number, they
should be 100. and it shows 100 when I do =sum(column), but adding the number
manually, it's NOT.

Can someone tell me Why and How to fix this?

Lots of thank.

Neon520

 

[Neon520]

Hi Joeu2004,

Thank you so much for your detail information.
This really give an in-depth info about how Excel interpret the data.
Your "tweak" to this might have solved my problem that I'm having already,
but just to make sure that you have experienced this also, I want to confirm
with you the following:

I know that you understand the problem can be "more" or "less" than 100%.
Here is what I understand from your tweak, it only solve the problem when it
is "more" than 100% (1-sum(B1:B4),4), how can it solve the issue when it's
"less" than 100%?

Please let me know if I misunderstand your tweak somehow, but I can't see
how it solve the problem if it's less than 100%? Or does the "less than
100%" issue even exist?

Thanks again for all your help.

Neon520

 

[joeu2004]

Here is what I understand from your tweak, it only
solve the problem when it is "more" than 100%
(1-sum(B1:B4),4), how can it solve the issue when
it's "less" than 100%?

It works in both cases. But the presumption is that sum(B1:B4) is
less than 100%. That might not be the case due to rounding. It would
be safer to replace ROUND with ROUNDDOWN.

 

[David Biddulph]

You haven't explained where your percentages come from, but I assume that
you haven't done what you were told to do earlier, which is to display the
numbers to a greater precision. My guess is that you dont have exactly 77%,
but that you've displayed it only to the nearest 1%.

 

[joeu2004]

the presumption is that sum(B1:B4) is less than
100%.  That might not be the case due to rounding.
 It would be safer to replace ROUND with ROUNDDOWN.

On second thought, this could cause more problems than it solves. It
is probably better to live with the fact that the manual (by-hand) sum
of the displayed percentages might be more or less than 100%.

The problem with ROUNDDOWN is: it accumulates all of the numerical
"error" into the last figure. That can skew the last figure
significantly.

The problem with ROUND is: it could cause 1-sum(B1:B4) to go
negative. That can be avoided by computing max(0,1-sum(B1:B4)). But
that's only a bandaid. It can still skew the last figure.

I do habitually use ROUND for the individual computations -- for
example, =round(A1/$A$6,4). But that is only to mitigate the
propagation of numerical "error" that is inherent in binary computer
arithmetic; that is, to ensure that WYSIWYG.

 

[Neon520]

Hi David,
You're exactly right. Basically it's a chain of calculation. I get the
percentage from another set of numbers of division and format the cell as
percentage and I'm using the percentage to calculate something else by
multiplication and format the cell with 2 digital decimal.

Since I don't have the exact percentage and it changes over time according
to the previous set of number. Are there better to accomplish this?

Thanks,
Neon520