Make a chart that ignores zero values

[Co]

Hi All,

I found this spreadsheet on the net:
http://spreadsheets.google.com/ccc?key=pMnTJJ1wlKk_rZ_TXRU7KIg#

It is a log for runners. The chart displays only the days that contain
some data.
Those are the days that a person did a run.
I'm tried this on my own chart but it always comes up with the line
dropping to zero
value when no data was found on that day.

How is this done?

Regards
Marco

 

[Andy Pope]

Hi,

Select the chart and then via Tools > Options > Chart > Plot empty cells as
Interpolated.

But this will only may a difference if you cells are truly empty. If they
contain formula or text that setting will have no effect.
Instead you need to use NA() instead of ""

Cheers
Andy

 

[Co]

Plot from =IF((B4>0),(C4/B4),NA()) instead of  =IF((B4>0),(C4/B4),"")

When I do this:

=IF((B12>0);(C12/B12);NA())

I get an error in the empty field:
#NAME?

If I do this in the field:
=IF((B12>0),(C12/B12),NA())

I get an error saying:
Error in the formula.

Marco

 

[Jon Peltier]

NA() and #N/A may have a different representation in a non-English
localization of Excel. Look up the "Information Function" for an error that
means "no value is available". and use this in your formula.

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


---------------------------------
When I do this:

=IF((B12>0);(C12/B12);NA())

I get an error in the empty field:
#NAME?

If I do this in the field:
=IF((B12>0),(C12/B12),NA())

I get an error saying:
Error in the formula.

Marco