Ignore Zeros in a Pie Chart

[tester100099]

Hello!

I'm working on creating a pie chart that draws data from a fair number of source cells. However, a good number of these cells = 0%. How can adjust the chart data range to ignore zeros.

Here is my chart data range:

='Jan-10'!$G$3:$G$18,'Jan-10'!$K$3:$K$18

Thanks!

 

[Jason Sidell]

Check out this link.

http://www.andypope.info/charts/piezeros.htm




Jason

 

[tester100099]

Jason,

Thanks for pointing me to that website, but I have already tried that and was unsuccessful.

What else can I try?

 

[tusharm]

Jason,

Thanks for pointing me to that website, but I have already tried that and was unsuccessful.

What else can I try?

What does that mean? That Andy's approach fails with a particular version of Excel? Or something else?

 

[tester100099]

After some more attempts with Andy's approach, I have been able to nearly get it to work. I get lost on how to use the "named ranges" step.

 

[Bill Rockenbach]

Whereever you have a zero (0) you do not want to reflect on the chart, change the zero to #N/A. Change the font color to the same color as the cell color to hide the text.

 

[tester100099]

Bill,

That solution is not an option for me since my data is formulated from other cells. Because of this, I do not want to have to change zeros to another value, unless I am able to add this into the formula. Is it a possibility to change the formula to replace zeros with #N/A automatically?

For reference, the data for my chart has cells formulated like "=$I10/$I$19"

 

[VoG]

Try

=IF($I10/$I$19=0,NA(),$I10/$I$19)

 

[Bill Rockenbach]

My previous post
Sorry I was thinking of a linear chart

 

[tester100099]

Peter,

Your method worked to turn all zeros into #N/A, but now Andy's method to remove zeros from the chart falls apart in the second column of "temporary data." Now, all values past this point show "#N/A" and give a "Value Not Available Error." I'm not sure why this is still not working....

Thanks to all for your help on this problem!