 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Charting / September 2007Charts recalculate to 100%
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 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, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ > We just ran into a real problem where the Charting on Excel will > recalculate your numbers. [quoted text clipped - 30 lines] > > Tom 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ > 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 [quoted text clipped - 54 lines] >> >> Tom 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 > 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 [quoted text clipped - 66 lines] >>> >>> Tom 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ > Which is the the problem. > [quoted text clipped - 86 lines] >>>> >>>> Tom |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.