I recently discovered a curious "feature" of Excel. If you create a pie
chart, and label the slices of the pie with data labels that include the
percentage, Excel sometimes adjusts the values in unexpected ways.

As far as I can tell, this only happens when the percentages are formatted
as whole-number percents (e.g., "57%"). In this situation, Excel will
adjust values so that the sum of all of the percentages around the pie is
exactly 100%.

I discovered this situation when I created a pie chart that had 7 slices.
The percentage associated with one of the slices appeared in the data as
"0.572702...". However, Excel displayed the value in the pie chart as
"56%". It did not seem to matter if I generated the pie from actual data,
or if I manually converted the data into percentages. As it happened,
several of the other values had been rounded up, so Excel took it upon
itself to change this value to compensate and make the percentages add to
exactly 100%.

Here are the percentages for each of the slices in the pie. The first
column of percentages is what's displayed if I format for 4 digits; the
second column is the display if I reduce to 1 decimal digit; and the third
column is the display if I show only whole-number percents:

1 - 2.0687% 2.1% 2%
2 - 3.7651% 3.8% 4%
3 - 0.7778% 0.8% 1%
4 - 3.0802% 3.1% 3%
5 - 18.5323% 18.5% 19%
6 - 57.2702% 57.3% 56% <===
7 - 14.5058% 14.5% 15%

Total 100.1% 100%

As you can see, when I format the data labels to display a decimal digit,
then Excel correctly rounds the displayed value for Slice 6 to "57.3%".
With this format, the sum of the percentages around the pie did not add up
to exactly 100% -- the sum of displayed values was 100.1% in this case. If
I formatted the data lables to display no decimal digits, but forced the
display to contain a decimal point, then Excel rounded the value for Slice 6
to "57.%". The unauthorized adjustment occurred only when I formatted for
whole-number percents.

Has anyone else out there observed this kind of behavior?


-- David Benson

If you search the Google newsgroup archives, you'll find other posts
about this. For example:


http://groups.google.ca/group/micros...9a8a77a6d6b07d

David Benson wrote:
I recently discovered a curious "feature" of Excel. If you create a pie
chart, and label the slices of the pie with data labels that include the
percentage, Excel sometimes adjusts the values in unexpected ways.

As far as I can tell, this only happens when the percentages are formatted
as whole-number percents (e.g., "57%"). In this situation, Excel will
adjust values so that the sum of all of the percentages around the pie is
exactly 100%.

I discovered this situation when I created a pie chart that had 7 slices.
The percentage associated with one of the slices appeared in the data as
"0.572702...". However, Excel displayed the value in the pie chart as
"56%". It did not seem to matter if I generated the pie from actual data,
or if I manually converted the data into percentages. As it happened,
several of the other values had been rounded up, so Excel took it upon
itself to change this value to compensate and make the percentages add to
exactly 100%.

Here are the percentages for each of the slices in the pie. The first
column of percentages is what's displayed if I format for 4 digits; the
second column is the display if I reduce to 1 decimal digit; and the third
column is the display if I show only whole-number percents:

1 - 2.0687% 2.1% 2%
2 - 3.7651% 3.8% 4%
3 - 0.7778% 0.8% 1%
4 - 3.0802% 3.1% 3%
5 - 18.5323% 18.5% 19%
6 - 57.2702% 57.3% 56% <===
7 - 14.5058% 14.5% 15%

Total 100.1% 100%

As you can see, when I format the data labels to display a decimal digit,
then Excel correctly rounds the displayed value for Slice 6 to "57.3%".
With this format, the sum of the percentages around the pie did not add up
to exactly 100% -- the sum of displayed values was 100.1% in this case. If
I formatted the data lables to display no decimal digits, but forced the
display to contain a decimal point, then Excel rounded the value for Slice 6
to "57.%". The unauthorized adjustment occurred only when I formatted for
whole-number percents.

Has anyone else out there observed this kind of behavior?


-- David Benson




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html