SOLVED

Contributor

Strange Behaviour With XY Graph and Text

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

8 Replies

Re: Strange Behaviour With XY Graph and Text

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.

Re: Strange Behaviour With XY Graph and Text

Thanks Sergei. Why does having the text there cause the numbers to display so strangely e.g. up to 250?
best response confirmed by CMSCMSCMS (Contributor)
Solution

Re: Strange Behaviour With XY Graph and Text

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.

Re: Strange Behaviour With XY Graph and Text

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

Re: Strange Behaviour With XY Graph and Text

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.

Re: Strange Behaviour With XY Graph and Text

Yes, that's what Sergei and I wrote.

Re: Strange Behaviour With XY Graph and Text

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".

No problem!