skip cells with zero values in chart (cells not empty)

[Guest]

I don't want to display zeros in my line chart. The cells are not empty -
they contain a formula. I have used a simple if statement to hide a zero
value - however the chart is still showing the datapoint as zero.

 

[David Biddulph]

What does your IF statement produce? If it gives an empty string "", then
it will be treated as zero. Change the "" to NA(). If you want to hide the
NA() values in the worksheet, you can do that by conditional formatting.

 

[Beverly Darvill]

I have an IF statement
=IF('Resource Availability New'!D10<>"", 'Resource Availability New'!D10,
0)/'L:\Project Control\Actuals\[Monthly Hours.xls]Sheet1'!N$2

but I don't want it to plot the 0 values but the result returned from the
statement is used further down the sheet in a sum formula to which I need to
see the result instead of NA how can I do that?

Beverly

 

[Jon Peltier]

Set up two ranges in the worksheet. We're well past the age of COBOL, where
we scrounged for every byte of file size.

One range keeps zeros for subsequent processing, and the other uses NA() for
charting. Since both ranges are linked to the original source, they should
stay synchronized.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/

Don't miss it! Only two weeks away!

Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______

I have an IF statement
=IF('Resource Availability New'!D10<>"", 'Resource Availability New'!D10,
0)/'L:\Project Control\Actuals\[Monthly Hours.xls]Sheet1'!N$2

but I don't want it to plot the 0 values but the result returned from the
statement is used further down the sheet in a sum formula to which I need
to
see the result instead of NA how can I do that?

Beverly

What does your IF statement produce? If it gives an empty string "",
then
it will be treated as zero. Change the "" to NA(). If you want to hide
the
NA() values in the worksheet, you can do that by conditional formatting.