Excel Incorrectly Plotting Non-sequential Data Points

[AaronKalb]

I am plotting a chart in Excel 2003 using non-sequential data points
as shown below.

Time Value
0 0.07
2 0.09
4 0.14
6 0.10
9 0.08
10 0.09

Excel, however, plots them in order as if they were:

Time Value
1 0.07
2 0.09
3 0.14
4 0.10
5 0.08
6 0.09

When I look at the Source Data, it highlights the right data, but just
seems to plot it incorrectly. Could this be a formatting error? Any
help is VERY appreciated.

 

[Bernie Deitrick]

Aaron,

Select both columns (not just the Value column) then use the Insert Chart command, and select XY
scatter.

HTH,
Bernie
MS Excel MVP

 

[AaronKalb]

Bernie,

Thank you for the help. Your method solved the problem that time, but
now it's not working now with the following set of data:

-1 0.07
0 0.07
2 0.09
4 0.14
6 0.10
9 0.08
10 0.09

I can create a perfect chart using rows the first 6 rows above, but as
soon as I add the last row of data, it wants to plot everything
sequentially as "1" through "7". I don't understand why this would
change just because of the "10".

 

[AaronKalb]

edit: ...change because of the "-1"

 

[Jerry W. Lewis]

It plots just fine as an "XY (Scatter)" chart in Excel 2000 and Excel 2003.

Most likely either
1. you are specifiying a "Line" chart (which is intended to behave as you
describe), or
2. some of your observations are not really numbers

To test for #2, try =COUNT(xdata) and =COUNT(ydata) to be sure that both are
returning 7 as expected.

Jerry

 

[AaronKalb]

Thanks Jerry!

The problem was with the cell containing "10". It was not recognized
as a number so the COUNT function was only returning a value of 6. I
re-formatted the cell and it now graphs as it should. Thanks again.

- Aaron

 

[Jerry W. Lewis]

You're welcome! Glad it helped.
Jerry