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!!!

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!!!

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!!!

use something like

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

if you use 2 decimals.

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


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

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


"Niek Otten" wrote:

use something like

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

if you use 2 decimals.

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


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

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?
--
David Biddulph

"Neon520" wrote in message
...
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


"Niek Otten" wrote:

use something like

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

if you use 2 decimals.

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


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

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 -

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 -

On Nov 19, 5:03*pm, Neon520 wrote:
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.