Solved: Pie chart data labels percent formatting

[quicksilver7]

Hi, this is my first post, so please bear with me. I have created some VBA code that creates a little over one hundred pie charts. The data labels look great in most instances, displaying the percentages to one decimal point (to avoid labels showing 0%), but on a few of the charts, if the value is an even number, it displays as, for example, 31.%. The line of code is below:


ActiveChart.SeriesCollection(1).DataLabels.NumberFormat = "##0.#%"


If I change the format to "##0.0#%" or "##0.#0%", then the formatting changes to two decimal places (30.98%), which solves the problem, but not in a way that I want. How can I get the example value to display as 31.0%?

I have looked, but have been unable to find any help which explains what these strange formatting symbols mean.

Thanks,

Dave

[Bob Phillips]

ActiveChart.SeriesCollection(1).DataLabels.NumberFormat = "##0.0%"


What's worse than a pie chart?

Two pie charts (replace two without any number greater than 1).

[IgnBan]

Not to discourage you about your project but just information about pie charts.
From one of my favorite sites (off course after VBA Express!:thumb )

http://www.perceptualedge.com/articles/visual_business_intelligence/save_the_pies_for_dessert.pdf

[quicksilver7]

That site is okay on a theoretical and perceptual basis, but it lists no VBA code at all.

In the interim I found the solution myself, just be fiddling around trying different combinations of characters:


ActiveChart.SeriesCollection(1).DataLabels.NumberFormat = "##0.0%"

[Bob Phillips]

That site is okay on a theoretical and perceptual basis, but it lists no VBA code at all.

I think if you read it, you will find that the article is disparaging about pie charts. Pie charts are not worth the space that they occupy on a screen or a piece of paper. They lamentably fail to convey information in a meanigful, easily digestible way, and often distort the data. Read Few, read Tufte, hell read Jon Peltier, we all know that pie charts are bad news.


In the interim I found the solution myself, just be fiddling around trying different combinations of characters:


ActiveChart.SeriesCollection(1).DataLabels.NumberFormat = "##0.0%"


You found? I think I gave you exactly that yesterday morning.

[quicksilver7]

I think if you read it, you will find that the article is disparaging about pie charts. Pie charts are not worth the space that they occupy on a screen or a piece of paper. They lamentable fail to convey information in a meanigful, easily digestible way, and often distort the data. Read Few, read Tufte, hell read Jon Peltier, we all know that pie charts are bad news.



You found? I think I gave you exactly that yesterday morning.

Sorry, my mistake. I thought that the VBA code you posted was a quote of the code that I posted, not your solution. A belated thank you for your post! Regardless, I didn't lie; I tried that line of code before I read your reply.

I agree with you for the most part about pie charts, but my client wants to see them so what can I do? If the data are different components of a whole and the user wants to compare them both with each other and with the total in an illustrative way, then pie charts aren't that bad.

[Bob Phillips]

I agree with you for the most part about pie charts, but my client wants to see them so what can I do? If the data are different components of a whole and the user wants to compare them both with each other and with the total in an illustrative way, then pie charts aren't that bad.

I know what you mean, we're just VBA tarts! But I can't agree with your last sentiment, I can never find anything good to say about pie charts (even worse, pie within a pie... yukk!)