Charts recalculate to 100%

[tshad]

We just ran into a real problem where the Charting on Excel will recalculate
your numbers.

Excel wants to make it 100%

The problem is that our numbers are not 100%.

For example:

I have a table:
A B C
35% 25% 0%

What we got was a Pie Chart that showed:

A B C
61% 39% 0%

I have another one that is11,26,11,1,2 which equates to (in percentage)
21.57,50.98,21.57,1.96,3.92. Now this equates to 100%. but the table I am
using only has whole numbers: 22,51,22,2,4 which equates to 101% (which is
incorrect). This is a rounding anomaly in that all the numbers rounded up -
not much you can do about that. But the problem chooses which number to
change to make it 100% (in this case it changed the 51 to a 50).

Is there a way to tell the Pie Chart not to adjust the numbers even if they
don't equal 100%?

The problem here is that if you look at the table and the chart, they are
different and it looks incorrect because it doesn't match.

Thanks,

Tom

 

[Jon Peltier]

This adjustment came relatively recently to Excel, IIRC. In Excel 97 and I
think 2000, the percentages were not tweaked, so three equal pie slices
would each say 33%. In either 2002 or 2003, Excel started rounding one of
the wedges the wrong way, to 34%, so that the sum was 100%. Maybe this was
to satisfy those brilliant CEOs and CFOs who could add percentages in a pie
chart and fretted that there was a 1% rounding discrepancy, but went down in
flames when the internet bubble burst.

Of course, if you want the pie chart to show 35%, 25%, and 0%, you could
select the Value option, not the Percentage option. If you want it to
accurately show 35% and 25% of a whole, you should add another wedge of 40%,
and format it with no lines and no fill. If you're wondering where 61% and
39% came from, you should calculate the percentages yourself: 35%/(35%+25%)
and 25%/(35%+25%), although I got 58% and 42%, as did my pie chart.

- Jon

 

[Jon Peltier]

Funny thing with that 33%-33%-34% pie chart, if you increase the decimals in
the number format, it becomes 33.3%-33.3%-33.3%. Maybe Excel assumes that
someone who understands decimals and percentages together is savvy enough to
cope with the totals not appearing to sum to 100%.

- Jon

 

[tshad]

Which is the the problem.

It is recalculating your numbers. If you had done your own calculations and
wanted to leave 20% out, you can't. Unless, as you stated, you use values
and not percentages - which seems to work. The only problem there is that
is doesn't create parts of the charts that are percentages of the whole. It
still shows the full chart.

For example, if I have the numbers 35 and 25 - I would want the pie chart to
show 35% of the pie and 25% of the pie with 40% missing. What it does is
show 61% of the pie and 39% of the pie even if the labels show correctly
when I use values instead of percentages.

It would be nice to have a setting that says to show the pie exactly as
given or to recalculate. You don't have this problem with the bar charts.
It shows as written.

Thanks,

Tom

 

[Jon Peltier]

I told you how to create a pie chart with part missing. If you were making a
100% stacked bar chart with only two percentages, you would have the same
problem, because Excel doesn't know you want it to fill in missing data.

Excel charts are only able to plot the data you tell it to plot, not make
calculations about the data so it can plot something else. For the chart to
do that, it would need (additional) hooks into the calculation mechanism,
and more complex UI for you to specify what to do with the data that you
want it to calculate. It's much easier and more flexible to plot the data
that is input, subject to the assumption that the user will provide the data
required to create the desired chart.

- Jon