HELP: Plotting 3 sets of different dates on same chart

Hi everyone,

I need to create a chart for work and am stuck on an issue w/ plotting dates. If anyone can help, I’d truly appreciate it. I’ve attached a copy of my document.

I have 3 different series: S, J, and D. Each series has a list of different dates, and I need to plot the results for each date, while comparing the 3 series. I have created 3 different charts to display the results for each series’ list of dates. However, I’d really like to chart all 3 series on one chart, so that you can see how the 3 compare. Does anyone know if that’s possible?

I believe my obstacle is that the list of dates are not consistent between the 3. I tried to get around this by creating one table with all the dates, and leaving blanks where necessary (on Sheet 2). However, this created a problem because the blanks showed on the charts as 0s.

Thank you in advance.

-L

Thanks for your response MrShorty. Yes, I did try that. The problem was that there were no lines connecting the dots. If I wanted to create the lines, I could only do so by adding 0 values for the dates w/out data, and then of course the lines dropped to 0 in those places.

You can set all those blanks in your table on Sheet2 to #NA, then they will be ignored by the graph, as shown in the attached file. Here's a quick way to achieve that:

First of all, click on the graph, then right-click and choose Change Chart Type, then select x-y scatter with lines joining the dots (I chose the 4th icon), then OK. It still looks a bit of a mess, but bear with me...

Then select the cells B2:D129, press the F5 key (GoTo), click on Special, then Blanks. Only the blank cells will now be highlighted. Enter this formula:

=NA()

but rather than pressing <Enter>, hold the CTRL key down and then press <Enter> - this will fill that formula in all the highlighted cells, and you can see a big improvement in what your graph looks like. Note that you have some entries for the green line of "<50", rather than actual numeric values, so these distort the graph in the middle - change them to numbers to avoid this.

You can get your table to look like it did before - Select the cells from B2 to D129. Click on Conditional Formatting | New Rule | Use a formula, and put this formula in the box:

=ISNA(B2)

Then click the Format button and in the Font tab choose white on the Color drop-down, then OK your way out. All those cells containing #N/A will now appear blank.

Hope this helps.

Pete