Using EXCEL Graph to show two series in different timeframes??!!

[ccat]

Dear all,

This is my first message posted with Tek-Tips, any help will be deeply appreciated.

I'm trying to get an Excel graph to show 2 series; one referencing 24 months of data [series A](e.g. 2001 and 2002 figures) and the other referencing only 12 months of data [series B](e.g. only 2002 figures).

Series A and B must be shown on the same graph and series B must appear in the right half on the graph because the x-axis goes from 2001 to 2002.

This may sound easy at first, but there are limitations to what can be done:

1. The data ranges for A and B must contain all 24 months data. (i.e. can't change the first 12 months of B to #n/a)

2. if all possible, I'm very reluctant to manually 'hide' the first 12 points on series B in the graph one by one because there is quite a number of such graphs that I need to do.

When I select the last 12 month of the data range for series B, Excel moves it to the left half of the graph (2001) even though it was meant to be 2002 data.

Thanks for your attention and patience with this long message. Please, HELP~~~~~~~~ ahahaa... >_<

Cheers,
ccat

 

[hauge]

I agree that it is somewhat peculiar that data points are moved to the left when changing the data range to include the last 12 observations.

But letting the data range include all 24 observations and just leaving the first 12 cells of the 2002 series blank seems to solve the problem for me. Excel (version 2002) seems to assume the value zero for the blank cells, but does not plot these values in the graph. What kind of graph are you making? I tried a standard bar and a line graph.

Cheers,
Hugo

 

[Hasit]

This may also help, particularly after hauge's contribution:

faq68-1277

This faq describes how you can plot data and get the graphing tool to ignore blanks or zeros.

 

[GlennUK]

You might want to try using a graph type of X-Y scatter, with the line between points showing.

This should show shorter series in the correct place, as one of the axes will be the date, and the points should be graphed at the correct time periods.

Glenn.