Charting Multiple Series Data with Dates?

[Guest]

Hello
I am plotting 5 data series on the same line chart. Dates are plotted on the x axis and concentrations of chloride on the y axis. The dates are formatted like "5/12/2003" as "general" data in the "alignment" menu of "format". The trick is, each data set does not have the exact same date range. For example, one sampling site may have 10 data points that range from 4/2/1991 to 5/12/2003 where as another sampling site may have 12 data points ranging from 4/30/1991 to 5/1/2003. That is to say that the sampling dates for each sampling site are not the same.
When I plot the data, and use "automatic" for the x axis, the first data series (for a sampling site) plots fine and shows the entire date range on the x axis. But, subsequent plots of other data points cause the chart to change, honoring the most recent data series added where the dates on the x axis compress to honor only the last series entered. The data for the first series is still on the plot but no longer has assigned date (x data) on the axis and when I point to a data point the x value is like "point 23" instead of say, 5/12/2003. Using "time-scale" results in several other problems, namely no dates on the x axis

Is the only way to solve this problem to make sure every sampling site has a date entered that matches ALL the dates for ALL sampling sites, even if a sample was not collected on that date? Why can't Excel just plot the data according to a time scale

I think I've tried everything. Any help would greatly be appreciated

 

[Nicky]

Hi,

It sounds like excel is not reading your x values as dates, but as text
labels. This means that it is not scaling the x axis chronologically
according to your highest and lowest dates, and setting x axis to ‘time
series’ loses the labels.

You say that the dates entered as “5/12/2003”. Does this include the
quotation marks? if so, this is probably the problem - try search and
replace to remove quotation marks in your x range. Alternatively,
retype the date values, if there are not many of them.

The attached sheet shows how it should be possible to chart series with
different time periods on the same chart. It is not even necessary to
have the dates in chronological order.

If this doesn't work, try attaching an example of what you're trying to
do - this will make diagnosis easier.

Attachment filename: time series.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=416884

 

[Nicky2]

Hi LIN,
click 'post reply', then type in your file name and path in the 'attach
file' box below (or click on the 'browse' botton to find and select
your file).

 

[Jon Peltier]

Bruce -

This is how line charts deal with category values. Even though the time
scale axis will adjust the spacing proportional to the days between data
points, it uses the dates for the first series for all the charted series.

If you change the chart to an XY Scatter chart, each series can have
distinct sets of x values. You lose some of the time scaling formatting
options in the Scatter chart, unfortunately. But you can keep the first
series as a line series, then change the others to scatter: right click
on the series to change, choose Chart Type, and pick an appropriate
scatter subtype.

- Jon

 

[Guest]

Jon and Nicki
Thanks for your reply. Unfortunately, and to my surprise, it doesn't look like Excel can do what I want it to do. I have tried your suggestion, but even after putting in a second data series and changing the chart for that series to a scatter chart, the result is the same. The dates associated with the second data set are what the chart chooses to use and the dates for the first data series are lost and assigned the dates of the second data set. So, the chart is wrong, that is the data are not presented correctly. After fighting this for two weeks, I must fall back to presenting each series on one chart, which is unfortunate. Maybe Microsoft should spend some of their mega bucks on this issue! Thanks again for your assistance

FYI, I do not have an "attach file" option on my reply message window, otherwise, I would have attached an example of my workbook from the start
-Bruce S.

 

[Jon Peltier]

Bruce -

I made it work in a sample workbook, so I know it can be done. If you
send me a simple workbook with the data and chart, I'll step through the
procedure I used, and return it to you.

- Jon

 

[Jon Peltier]

That's what I meant, so we don't annoy the ng police, he he.

- Jon