Strange Behaviour With XY Graph and Text

[CMSCMSCMS]

Hi folks,

I'm trying to understand the behaviour of an XY scatter graph. Spreadsheet at the following link https://www.dropbox.com/s/1otozhfjqmwkpz4/Book2.xlsx?dl=0 

I want to plot two columns on an XY scatter graph. Column A has numbers from 0 to 50, and column B from 0 to 4. If I create an XY scatter graph with column B as the X axis and A as the Y axis (which is how I want the data displayed) then the values don't seem to display correctly, that is the values shows from 0 to 250, rather than 0 to 4. If I plot the other way around i.e. B on Y and A on X, the data displays correctly.

I've discovered the cause, which is that in cell B191 there's the text "N/A". If I remove that text it displays correctly, so the questions I have are:

1. Why does the text cause the chart to display incorrectly?

2. Why when I plot B on Y and A on X does it display correctly?

Thanks

[#2]

@CMSCMSCMS 

Both since you have text (N/A) within numbers for X axis. If instead of the text you use =NA() which returns #N/A error and add to options

chart works correctly.

[#3]

Thanks Sergei. Why does having the text there cause the numbers to display so strangely e.g. up to 250?

[#4 · Accepted solution]

@CMSCMSCMS 

The x-values of an XY Scatter chart should not be text. Cell B191 contains the text value N/A. This causes Excel to use sequential numbers 1 ... 227 for the x-axis.

If you either change B191 to the error value #N/A or clear this cell, the chart will look the way you want.

 

[#5]

Thanks Hans, that explains it. I was trying to figure out where it got those values from.

[#6]

Actually, text in the Y value range will be plotted as zero. It's text within the X values which change these X values from numerical values to simple counting numbers from 1 to the number of points.

[#7]

@JonPeltier 

Yes, that's what Sergei and I wrote.

[#8]

@Hans Vogelaar 

Sorry, I didn't mean to step on your post. When I read it, I thought it said "the values of an XY Scatter chart".

[#9]

@JonPeltier 

No problem!