Omit plotting zero values

[andyditchfield]

Hi

I have 3 columns of time series data but for one of the columns I only have values at specific points with the intervening points being zero (not blank).

Is there a way to only plot the values on the chart rather than the zero's? I can cheat and do it by altering the Y-axis scale to start at 0.1 and format the axis to 0 decimal places but then I lose negative numbers......

Values for chart as follows:

Date RPI CPI M&B
Jan-03 2.9% 1.4%
Feb-03 3.2% 1.6%
Mar-03 3.1% 1.5%
Apr-03 3.1% 1.5%
May-03 3.0% 1.3% -
Jun-03 2.9% 1.0%
Jul-03 3.1% 1.4%
Aug-03 2.9% 1.4%
Sep-03 2.8% 1.5% (0.4%)
Oct-03 2.6% 1.4%
Nov-03 2.5% 1.4%
Dec-03 2.8% 1.2%
Jan-04 2.6% 1.4%
Feb-04 2.5% 1.3%
Mar-04 2.6% 1.1%
Apr-04 2.5% 1.1%
May-04 2.8% 1.4% 5.3%
Jun-04 3.0% 1.7%
Jul-04 3.0% 1.3%
Aug-04 3.2% 1.3%
Sep-04 3.1% 1.1% 5.6%

Any help most appreciated........

 

[Fazza]

Hi Andy.

A usual approach relies on #N/A not plotting. So, have a formula like below so that zero values become #N/A and use that instead of the zero.

HTH, Fazza

=if(a1=0,na(),a1)

 

[andyditchfield]

Many thanks Fazza, that works well - strangely my formula had #n/a in originally before I removed it with a =if(isna( formula and before I plotted the graph.........

 

[Fazza]