Need a chart with x-axis (date) data in two columns

[GarrettS]

I am trying to create a chart from four columns of data:

Column 1: Date
Column 2: First Data corresponding to the adjacent date in column 1
Column 3: Date
Column 4: Second Data corresponding to the adjacent date in column 3

I tried a scatter chart with two series, the first with column 1 as X axis and column 2 as Y axis, and the second series using columns 3 and 4, but instead of using the data in columns 1 and 3 for the x axis it uses the row number of columns 2 and 4.

How can I chart this correctly?

Thanks in advance.

 

[Andrew Poulsom]

You can plot noncontiguous ranges by adjusting the series references to eg:

X Values =(Sheet1!$A$2:$A$10,Sheet1!$C$2:$C$10)
Y Values =(Sheet1!$B$2:$B$10,Sheet1!$D$2:$D$10)

 

[GarrettS]

I'm not sure what you mean.

First I highlight all four columns and choose a scatter chart. The default is four series, two for the columns I intend to be plotted and two for the date columns.

I delete the date series and am left with my data correctly plotted on the y axis. The X Values field for both series' are left blank while the Y Values field is the single column the data is in: =$Q$2:$Q$11287 and for the other series: =$S$2:$S$11287

The X axis however goes from 0 to 11287. If I fill $P$2:$P$11287 into the X axis for series 1 and column R for X axis for series 2, the chart x axis goes from 1 to ~45000 but the data is still plotted from 1 to 11287. If I enter both of my data columns for the X axis data for either series, excel crashes.

 

[Andrew Poulsom]

Start by plotting the first series. Then change the Series formula as I showed you above.

 

[GarrettS]

Sorry for the confusion, I think I poorly explained my data.

The data in the 2nd and 4th columns are different series. One is a bid and the other an offer. The dates in columns 1 and 3 cover the same date range but may be at different points during that range.

So 10am on Feb 20th, 2011 I want to be plotting the bid and the offer, but these points are not in the same excel row.

 

[Andrew Poulsom]

You need 2 series, eg:

Series1

X Values =Sheet1!$A$2:$A$10
Y Values =Sheet1!$B$2:$B$10

Series2

X Values =Sheet1!$C$2:$C$10
Y Values =Sheet1!$D$2:$D$10

and an XY Scatter chart.

 

[GarrettS]

Thanks. This is what I have tried but instead of using the selected X Values range, Excel is still using the row number for the x value.

 

[VoG]

Try this: select the first two columns and plot an X-Y scatter chart. Select the second two columns and Copy. Activate the chart, Edit > Paste Special, tick New Series and X values in first column.

 

[GarrettS]

Thanks! That did the trick.