Multiple data series charting hiding zero values

[AWAmsc]

I have a question that I'm not sure Excel is capable of doing. I have 2 series of data, with potentially more series in one chart. These are time series based data. Here is an example:

ColA ColB ColC ColD ColE
X-Axis Ser1 Ser2
2001/03 214
2001/04 194
2001/05 267
2001/06 250
2001/07 345
2001/08 382
2001/09 360 2
2001/10 377 11
2001/11 276 19
2001/12 195 23
2002/01 192 27
2002/02 185 29

I have a simple line chart and want to chart these on the same timeline without having zero values for Ser2 from 2001/03 to 2001/08. These values are based on 2 pivot tables, so the values you see above are formulas. The following are the formulas:

ColumnA is just the text of the dates to serve as lookup values on the pivot table

ColumnB has this formula
=IF(ISERROR(D2),0,D2)

ColumnC has this formula
=IF(ISERROR(E2),"",E2)

ColumnD has this formula
=VLOOKUP(A2,Tables!$A$8:$B$19,2,FALSE)

Column E has this formula
=VLOOKUP(A2,$A$6:$B$17,2,FALSE)

I have a "" in the formula for columnC but I still get zero values on the chart. I have also tried selecting the data from 2001/09 through 2002/02 for the data series, but then the data points are charted at the beginning of the time series and not at the appropriate x-axis coordinate.

Any suggestions?

 

[Dave Hawley]

Hi

Must admit I haven't read all your question, but try returnig #N/A by using NA() function in C, instead of "" . Hiding rows can also work, but is more cumbersome.

http://www.ozgrid.com/Excel/ChartTips.htm

 

[AWAmsc]

You da Man!

I didn't consider using that because I had different problems with the return of an error which shows as #N/A.

Thank you!