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