In my spreadsheet I have 6 cells that always sum to equal 100%. I am having
troubles showing the values as whole numbers that will always will equal
100%. For example I ran into the following problem:

50.585%
48.903%
00.512%
00.000%
00.000%
00.000%
total = 100%

My spreadsheet shows:
51%
49%
1%
0%
0%
0%
Total equals 101%

How do I get it to show:
51%
49%
0%
0%
0%
0%
Total Equals 100%

Thanks,

Curt J

Hi,

Because you are using round the values are being changed. Format the
percentage cell to show zero decimal places instead of using round and your
sum will be correct.

Remember rounding and its variants change the value of a cell compared to
formatting which changes the way you see the value.

Mike

"Curt J" wrote:

In my spreadsheet I have 6 cells that always sum to equal 100%. I am having
troubles showing the values as whole numbers that will always will equal
100%. For example I ran into the following problem:

50.585%
48.903%
00.512%
00.000%
00.000%
00.000%
total = 100%

My spreadsheet shows:
51%
49%
1%
0%
0%
0%
Total equals 101%

How do I get it to show:
51%
49%
0%
0%
0%
0%
Total Equals 100%

Thanks,

Curt J

Hi Mike, thanks for responding to my post.

I actually am using formatting to show the percentage to show zero decimal
places. I also tried the round function and that didn't work either.

"Mike H" wrote:

Hi,

Because you are using round the values are being changed. Format the
percentage cell to show zero decimal places instead of using round and your
sum will be correct.

Remember rounding and its variants change the value of a cell compared to
formatting which changes the way you see the value.

Mike

"Curt J" wrote:

In my spreadsheet I have 6 cells that always sum to equal 100%. I am having
troubles showing the values as whole numbers that will always will equal
100%. For example I ran into the following problem:

50.585%
48.903%
00.512%
00.000%
00.000%
00.000%
total = 100%

My spreadsheet shows:
51%
49%
1%
0%
0%
0%
Total equals 101%

How do I get it to show:
51%
49%
0%
0%
0%
0%
Total Equals 100%

Thanks,

Curt J

There isn't a universally right way to do this. For instance what about:

49.512%
49.512%
0.978%
-------
100.00%

will round/display (to the nearest percent) as

50%
50%
1%
----
101%

or perhaps

49%
49%
1%
----
99%

What would *your* preferred answer be in that case? Can you describe, in
words, an algorithm that XL should use to make the choice?


In article ,
Curt J wrote:

Hi Mike, thanks for responding to my post.

I actually am using formatting to show the percentage to show zero decimal
places. I also tried the round function and that didn't work either.

"Mike H" wrote:

Hi,

Because you are using round the values are being changed. Format the
percentage cell to show zero decimal places instead of using round and your
sum will be correct.

Remember rounding and its variants change the value of a cell compared to
formatting which changes the way you see the value.

Mike

"Curt J" wrote:

In my spreadsheet I have 6 cells that always sum to equal 100%. I am
having
troubles showing the values as whole numbers that will always will equal
100%. For example I ran into the following problem:

50.585%
48.903%
00.512%
00.000%
00.000%
00.000%
total = 100%

My spreadsheet shows:
51%
49%
1%
0%
0%
0%
Total equals 101%

How do I get it to show:
51%
49%
0%
0%
0%
0%
Total Equals 100%

Thanks,

Curt J

Hello,

My preferred answer is 51%,49%,0%.

My decision making criteria is that .903 & .585 are closer to 1 than .512 so
therefore I will round up on 50.585 and 48.903. Since .512 is smallest it
will not get rounded up.

Consequently, if I had the values:

50.440%,
40.460%
9.100%
------
100%

By formatting how I currently have it, my results would be
50%
40%
9%
-----
99%

With the same decision making, .46% is closest to 1 so I would round up.
The results would therefore be 50%,41%,9% =100%

Thanks for your help,

Curt J

"JE McGimpsey" wrote:

There isn't a universally right way to do this. For instance what about:

49.512%
49.512%
0.978%
-------
100.00%

will round/display (to the nearest percent) as

50%
50%
1%
----
101%

or perhaps

49%
49%
1%
----
99%

What would *your* preferred answer be in that case? Can you describe, in
words, an algorithm that XL should use to make the choice?


In article ,
Curt J wrote:

Hi Mike, thanks for responding to my post.

I actually am using formatting to show the percentage to show zero decimal
places. I also tried the round function and that didn't work either.

"Mike H" wrote:

Hi,

Because you are using round the values are being changed. Format the
percentage cell to show zero decimal places instead of using round and your
sum will be correct.

Remember rounding and its variants change the value of a cell compared to
formatting which changes the way you see the value.

Mike

"Curt J" wrote:

In my spreadsheet I have 6 cells that always sum to equal 100%. I am
having
troubles showing the values as whole numbers that will always will equal
100%. For example I ran into the following problem:

50.585%
48.903%
00.512%
00.000%
00.000%
00.000%
total = 100%

My spreadsheet shows:
51%
49%
1%
0%
0%
0%
Total equals 101%

How do I get it to show:
51%
49%
0%
0%
0%
0%
Total Equals 100%

Thanks,

Curt J

So you'd round one instance of 49.512% up to 51%, one instance of
49.512% down to 49% and 0.978% to 1%?

I can't make that work...

0.978% rounds up to 1%, leaving 99%. Since there are only two values,
one instance of 49.512% will have to round up to 50%, one down to 49%.
How should XL decide which one?

Or, if I use your logic with, say:

12.41%
12.41%
12.41%
12.41%
24.39%
24.39%
0.53%
0.53%
0.52%

0.53% is the highest fractional value, so it rounds up to 1%.
0.52% is next, so it rounds up to 1%
0.41% is next, so 12.41% goes to 13%

100% - 3*1% - 4*13% leaves 45%, so one instance of 24.39% will be
rounded down to 23% and one instance will be rounded down to 22%.

Is that what you're after?

In article ,
Curt J wrote:

My preferred answer is 51%,49%,0%.

My decision making criteria is that .903 & .585 are closer to 1 than
.512 so therefore I will round up on 50.585 and 48.903. Since .512
is smallest it will not get rounded up.

<snip

There isn't a universally right way to do this. For instance what about:

49.512%
49.512%
0.978%
-------
100.00%

Curt,

If you always want the items to add to a single fixed value, your last calc needs to be

=FixedValue - SUM(Other rounded Values)

like this, for percentage

=1-SUM(B2:B3)

HTH,
Bernie
MS Excel MVP


"Curt J" wrote in message
...
Hello,

My preferred answer is 51%,49%,0%.

My decision making criteria is that .903 & .585 are closer to 1 than .512 so
therefore I will round up on 50.585 and 48.903. Since .512 is smallest it
will not get rounded up.

Consequently, if I had the values:

50.440%,
40.460%
9.100%
------
100%

By formatting how I currently have it, my results would be
50%
40%
9%
-----
99%

With the same decision making, .46% is closest to 1 so I would round up.
The results would therefore be 50%,41%,9% =100%

Thanks for your help,

Curt J

"JE McGimpsey" wrote:

There isn't a universally right way to do this. For instance what about:

49.512%
49.512%
0.978%
-------
100.00%

will round/display (to the nearest percent) as

50%
50%
1%
----
101%

or perhaps

49%
49%
1%
----
99%

What would *your* preferred answer be in that case? Can you describe, in
words, an algorithm that XL should use to make the choice?


In article ,
Curt J wrote:

Hi Mike, thanks for responding to my post.

I actually am using formatting to show the percentage to show zero decimal
places. I also tried the round function and that didn't work either.

"Mike H" wrote:

Hi,

Because you are using round the values are being changed. Format the
percentage cell to show zero decimal places instead of using round and your
sum will be correct.

Remember rounding and its variants change the value of a cell compared to
formatting which changes the way you see the value.

Mike

"Curt J" wrote:

In my spreadsheet I have 6 cells that always sum to equal 100%. I am
having
troubles showing the values as whole numbers that will always will equal
100%. For example I ran into the following problem:

50.585%
48.903%
00.512%
00.000%
00.000%
00.000%
total = 100%

My spreadsheet shows:
51%
49%
1%
0%
0%
0%
Total equals 101%

How do I get it to show:
51%
49%
0%
0%
0%
0%
Total Equals 100%

Thanks,

Curt J

On Nov 20, 9:58*am, Curt J wrote:
My preferred answer is 51%,49%,0%.
My decision making criteria is that .903 & .585 are closer to 1 than .512

You are not responding to JE's question. You are referring to
__your__ example, not his. In JE's example, the last item was about
0.9%. If this were a report of the contributions of 3 different sales
forces, I suspect that Sales Force C would be very angry if you
represented them by 0%. As you say, 0.9 is close to 1 (obviously).

The real take-away from this discussion is that there is __no__
general solution that will result in exactly 100% in all cases.

In specific cases, sometimes you can fudge the numbers to make them
work out. But generally, reports that include percentages (or any
other rounded numbers) have a footnote that says, in effect, the sum
of numbers might be more or less than 100% (in this case) due to
rounding. Simple as that.



so
therefore I will round up on 50.585 and 48.903. *Since .512 is smallest it
will not get rounded up.

Consequently, if I had the values:

50.440%,
40.460%
9.100%
------
100%

By formatting how I currently have it, my results would be
50%
40%
9%
-----
99%

With the same decision making, .46% is closest to 1 so I would round up. *
The results would therefore be 50%,41%,9% =100%

Thanks for your help,

Curt J



"JE McGimpsey" wrote:
There isn't a universally right way to do this. For instance what about:

* *49.512%
* *49.512%
* * 0.978%
* *-------
* 100.00%

will round/display (to the nearest percent) as

* *50%
* *50%
* * 1%
* *----
* 101%

or perhaps

* * 49%
* * 49%
* * *1%
* *----
* * 99%

What would *your* preferred answer be in that case? Can you describe, in
words, an algorithm that XL should use to make the choice?

In article ,
*Curt J wrote:

Hi Mike, thanks for responding to my post.

I actually am using formatting to show the percentage to show zero decimal
places. *I also tried the round function and that didn't work either.