I need to hide points on a line chart if the value is ZERO

[Ralph D''Andrea]

I'm using a line chart to display my info. I only have to show the points on
the graph. I found where to hide the line so only the points show. The only
problem is if I have a value of zero it's displaying a point at ZERO on the
axis, this is not what I want. I can't find where / how to (remove or hide)
point if a value of ZERO. Is this possible?

Please advise

 

[Andy Pope]

Hi,

If you do not want the zero to show you can do one of the following
- delete it and have empty cell instead
- type #N/A
- use formula =NA()

Select chart and use Tools > Options > Chart and make sure the Plot empty
cells as: is not set to zero.

Have to ask though why, in your case, is zero not a valid number that should
be shown?

Cheers
Andy

 

[Ralph D''Andrea]

Hi Andy,
I'd be more than happy to explain:
I have a sales chart that tracks activity for given months (Aug - Dec). At
present the only data I have to report on is Aug & Sept. Oct - Dec are
showing as zero on the axis with a dot. The client doesn't want to see zeroes
for a time in the future, if it was current month(sept) than it would be
valid.
I did like you suggested Tools>Options>Chart, but the the selection you told
me to check to make sure 'blank if zero' is not a field I can select. All the
selections in the top half of the screen are protected.

I have a formula in the cell that holds the result of a months activity. If
I was to put =(NA) there it would delete the formula. This is not something I
want to do. I don't want the client to have to go in every month and have to
change / modify various cells in more than a dozen charts. The intent was to
define the formulas and links, so all they have to do is import the data into
a defined worksheet and the charts will be populated accordingly.

Hope I gave you enough information.

Thanks for the response
Please advise,
Ralph

 

[Andy Pope]

Hi,

Assuming your data is in A1:B5 then you would put formula in C1 drag down to
C5 and then chart the data in C1:C5.

Aug 10 =IF(B1>0,B1,NA())
Sep 15
Oct 0
Nov 0
Dec 0

Of course if for some reason the sales for Oct where zero then the data
point would not show.

As for the Tools>Options>Chart setting being protected this would be caused
by either the sheet being protected or the chart not being selected.

Cheers
Andy

 

[Ralph D''Andrea]

Thanks Andy, works great