XY Scatter Problem - strange!

[Andrea Jones]

I am trying to plot an XY scatter graph with 2 series from this data:

Year 1996 1997 1998 1999 2000 2001 2002 2003
National rail 761 801 846 892 947 957 960 976
London Underground 784 772 832 866 927 970 953 942

However, it picks up the cells in column B (values 761 and 784) with the
series labels so they show as 'National Rail 761' and 'London Underground
784' and the data points are missing from the series. When I try to correct
these by setting up the series X Values and Y Values I then get an extra data
point at 0 around 1996 and the X Values change to 1,2,3,4,5 despite me
setting up the year values as the X values and them definitely being in
number format (not date format). I've tried everything I can think of but
can't get Excel 2000 to behave itself, has anyone else had this problem?

Andrea Jones

 

[Debra Dalgleish]

It sounds like the first values are in the same cell as the labels,
separated by a few space characters, and the second column is empty. If
you autofit the columns, you might be able to see better, what's
actually in each cell.

 

[Andrea Jones]

No they're not! They're definitely in separate cells, furthermore Excel
insists on initially picking up something like Sheet1!$A$2:$B$2 as the series
name, this gives something like 'National Rail 761'. When I correct this I
and set the X values to Sheet1!$B$2$I$2 and Y values to Sheet1!$B$3$I$3 I
then get a strange zero point added for 1996, try it yourself in Excel 2000
and see what happens for you!

Andrea Jones

 

[Andrea Jones]

Well, I really don't know what was going on but I noticed a similar thing
happened when I plotted a column chart from my data. I re-entered the data a
few cells below and it plotted fine! I then deleted the old cells and
formatted them all as numeric, moved my new data and everything worked OK, I
just can't figure out what was happening before as everything was in its own
cell and I'd plotted loads of other graphs from this data with no problem!

Andrea Jones

 

[Debra Dalgleish]

Thanks for reporting what you did to fix the problem.

Somehow, those numbers were converted to text, so Excel was "helping"
you, and including them in the labels.

If it happens again, there are some techniques here that you can use to
convert the text to numbers, so you don't have to retype them:

http://www.contextures.com/xlDataEntry03.html

 

[Andrea Jones]

Yes, you're right, that must have caused it! I checked the years weren't
formatted as text but I didn't think the data values would have ended up as
text, I can't remember what data I had in the sheet before but I must have
copied over some old data or something. At least it's sorted now.

Andrea Jones