There are two ways I would have dealt with this issue: (1) using a number format of 0.0%, as in the right chart above, coincidentally also the right-hand chart above, and (2) using custom data labels from the worksheet that show the correctly rounded percentages. My colleague John Mansfield has noticed in meetings with these same pointy-haired administrators that they compare the percentages on these pie charts with the percentages calculated in the worksheet, and they get just as exercised when these percentages don’t match as they do when the total percentage adds to 99 or 101 due to rounding error. I suspect this obviously intentional misstatement of percentage was implemented to mollify the high level pointy-haired managers, who deal with transactions worth billions of dollars, but worry that the percentages in their pie charts add to 100. The second chart, with one added decimal digit of precision, correctly displays 33.3% for all three wedges. However, the first chart reports percentages of 34%, 33%, and 33%. Each pie wedge is 1/3 of the total, 33.333333…%, rounded to 33%. Both charts below use the same data range, three cells each containing the value 1. This only occurs if the precision of the labels is unit percentages (number format of 0%), not if more precision is allowed (number format of 0.0%). The phenomenon is that Excel will place incorrect percentage labels onto the wedges of a pie chart, simply to ensure that the displayed percentages add to 100. This is a specific illustration of a strange and wonderful phenomenon in Excel pie charts. I do have one (or more) of those rants posts in me somewhere, but this isn’t one. This isn’t the usual “Pie Charts Suck” entry that we see so many of.
0 kommentar(er)
