hiding zero values in charts
 

Hi,

I followed the directions given to hide zero values in chats and tables in
Excel 2000. I used the chart wizard when i created them. But the zero
values continue to be shown on the chart. For example, I have data for
target values versus actual values for events. The targeted numbers go from
July 2005 - June 2005. The actual data values are from July 2005 - Nov
2005. I used the tools optionschartplot visible cells only.

Any guidance on what I am doing incorrect?

hiding zero values in charts
 

Hi fascal,

The setting you want is toolsoptionschartPlot empty cells as: not plotted
(leave gaps)

The problem you then face is that zero values are still plotted, where you
have no data you need to leave the cell empty or put the value #N/A in the
cell.

If you are using formulas for the actuals, try something like;
=IF(C3=0,NA(),C3)

Ed Ferrero
http://www.edferrero.com


Hi,

I followed the directions given to hide zero values in chats and tables in
Excel 2000. I used the chart wizard when i created them. But the zero
values continue to be shown on the chart. For example, I have data for
target values versus actual values for events. The targeted numbers go
from
July 2005 - June 2005. The actual data values are from July 2005 - Nov
2005. I used the tools optionschartplot visible cells only.

Any guidance on what I am doing incorrect?

I used the NA solution and it worked on the graph but the spreadsheet doesnot add up now as it now has NA!!!!!!!!!!!!!!!!!!!!

hiding zero values in charts
 

I suggest you keep the original data range as is for the table and
subsequent calculations, and use a nearby worksheet range for the chart
source data. Use a formula following Ed's suggestion to convert blanks,
text, and zeros to NA(), and use this fixed up range for the chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


"Peter Nash" wrote in message
...

Ed Ferrero Wrote:
Hi fascal,

The setting you want is toolsoptionschartPlot empty cells as: not
plotted
(leave gaps)

The problem you then face is that zero values are still plotted, where
you
have no data you need to leave the cell empty or put the value #N/A in
the
cell.

If you are using formulas for the actuals, try something like;
=IF(C3=0,NA(),C3)

Ed Ferrero
http://www.edferrero.com


Hi,

I followed the directions given to hide zero values in chats and
tables in
Excel 2000. I used the chart wizard when i created them. But the
zero
values continue to be shown on the chart. For example, I have data
for
target values versus actual values for events. The targeted numbers
go
from
July 2005 - June 2005. The actual data values are from July 2005 -
Nov
2005. I used the tools optionschartplot visible cells only.

Any guidance on what I am doing incorrect?

I used the NA solution and it worked on the graph but the spreadsheet
doesnot add up now as it now has NA!!!!!!!!!!!!!!!!!!!!


--
Peter Nash

hiding zero values in charts
 

thank you. I used the formula.

Best Regards,
fascal

"fascal" wrote:

Hi,

I followed the directions given to hide zero values in chats and tables in
Excel 2000. I used the chart wizard when i created them. But the zero
values continue to be shown on the chart. For example, I have data for
target values versus actual values for events. The targeted numbers go from
July 2005 - June 2005. The actual data values are from July 2005 - Nov
2005. I used the tools optionschartplot visible cells only.

Any guidance on what I am doing incorrect?