Apr 30 2021 03:19 AM
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
Apr 30 2021 03:47 AM
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.
Apr 30 2021 03:52 AM
Apr 30 2021 03:55 AM
SolutionThe 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.
Apr 30 2021 03:58 AM
May 04 2021 09:20 AM
May 04 2021 12:16 PM
Yes, that's what Sergei and I wrote.
May 04 2021 12:27 PM
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".
Apr 30 2021 03:55 AM
SolutionThe 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.