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" wrote:

On Nov 19, 1:50 pm, Neon520 wrote:
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 -----

On Nov 19, 1:50 pm, Neon520 wrote:
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" wrote:
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.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Neon520" wrote in message
...
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!!!- Hide quoted text -

- Show quoted text -