Rounding Question

[Curt J]

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

 

[Mike H]

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]

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.

 

[JE McGimpsey]

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%

 

[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]

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?

 

[Bernie Deitrick]

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]

Oh okay, now I understand. I missed it the first time.

In your example of 49.512, 49.512 and .978 I would like my answer to be
50%,49%,1%. In the event where .512=.512, I am not concered which goes to
50%. My cells are in J37:J42. Possible decision criteria where 2 values
equal could be that J37 get rounded up over J38, and J38 gets rounded up over
J39 etc.

In your other example, since .39 is the lowest fraction value I'd like one
of them rounded up. I would like the result to be:

12%
12%
12%
12%
25%
24%
1%
1%
1%

Thanks

Curt

 

[joeu2004]

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

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

 

[joeu2004]

If you always want the items to add to a single fixed value,
your last calc needs to be
=FixedValue - SUM(Other rounded Values)

I made the same mistaken suggestion yesterday in another thread, and
I've been guilty of doing this in spreadsheet. But then I realized
that it is wrong, or at least misleading.

For example, consider the numbers 50.4%, 48.4%, 0.4%, 0.4%, 0.4%. If
we round the first 4 and compute the 5th by 1-sum(first 4), we will
get 50%, 48%, 0%, 0%, 2%. Obviously the 5th percentage is a
misrepresentation. The problem is: all of the rounding "error" is
accumulated into the last formula.

Moreover, if the sum of N rounded percentages might exceed 100%, so
can the sum of N-1 rounded percentages. So 1-sum(other rounded
values) might go negative. Example: 50.5%, 48.9%, 0.5%, 0.1%. The
first 3 become 51%, 49% and 1%, which sum to 101%. So the 4th,
evaluated as 1-sum(...), becomes -1%.

Of course, that can be avoided by max(0,1-sum(other rounded values)),
or rounding down all other percentages.

But we are still stuck with the first problem, namely: accumulating
all of the rouding "error" into the last formula.

Aside: I wonder if using banker's rounding would always make 1-sum
(...) work insofar as avoiding negative results. But even banker's
rounding suffers from the accumulation flaw. Consider my second
example. I believe that with banker's rounding, the first 3 become
50%, 49%, 0%, which sums to 99%. But the 4th becomes 1%, which is a
misrepresentation.