Forum Discussion
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
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.
8 Replies
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.
- 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.
Yes, that's what Sergei and I wrote.
- Thanks Hans, that explains it. I was trying to figure out where it got those values from.
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.
- Thanks Sergei. Why does having the text there cause the numbers to display so strangely e.g. up to 250?
