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?
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?