SOLVED

Strange Behaviour With XY Graph and Text

%3CLINGO-SUB%20id%3D%22lingo-sub-2310034%22%20slang%3D%22en-US%22%3EStrange%20Behaviour%20With%20XY%20Graph%20and%20Text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2310034%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20folks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20understand%20the%20behaviour%20of%20an%20XY%20scatter%20graph.%20Spreadsheet%20at%20the%20following%20link%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.dropbox.com%2Fs%2F1otozhfjqmwkpz4%2FBook2.xlsx%3Fdl%3D0%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.dropbox.com%2Fs%2F1otozhfjqmwkpz4%2FBook2.xlsx%3Fdl%3D0%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20plot%20two%20columns%20on%20an%20XY%20scatter%20graph.%20Column%20A%20has%20numbers%20from%200%20to%2050%2C%20and%20column%20B%20from%200%20to%204.%20If%20I%20create%20an%20XY%20scatter%20graph%20with%20column%20B%20as%20the%20X%20axis%20and%20A%20as%20the%20Y%20axis%20(which%20is%20how%20I%20want%20the%20data%20displayed)%20then%20the%20values%20don't%20seem%20to%20display%20correctly%2C%20that%20is%20the%20values%20shows%20from%200%20to%20250%2C%20rather%20than%200%20to%204.%20If%20I%20plot%20the%20other%20way%20around%20i.e.%20B%20on%20Y%20and%20A%20on%20X%2C%20the%20data%20displays%20correctly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20discovered%20the%20cause%2C%20which%20is%20that%20in%20cell%20B191%20there's%20the%20text%20%22N%2FA%22.%20If%20I%20remove%20that%20text%20it%20displays%20correctly%2C%20so%20the%20questions%20I%20have%20are%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Why%20does%20the%20text%20cause%20the%20chart%20to%20display%20incorrectly%3F%3C%2FP%3E%3CP%3E2.%20Why%20when%20I%20plot%20B%20on%20Y%20and%20A%20on%20X%20does%20it%20display%20correctly%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2310034%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2310180%22%20slang%3D%22en-US%22%3ERe%3A%20Strange%20Behaviour%20With%20XY%20Graph%20and%20Text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2310180%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F569472%22%20target%3D%22_blank%22%3E%40CMSCMSCMS%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBoth%20since%20you%20have%20text%20(N%2FA)%20within%20numbers%20for%20X%20axis.%20If%20instead%20of%20the%20text%20you%20use%20%3DNA()%20which%20returns%20%23N%2FA%20error%20and%20add%20to%20options%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20306px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277009iB590CADB45815599%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Echart%20works%20correctly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2310201%22%20slang%3D%22en-US%22%3ERe%3A%20Strange%20Behaviour%20With%20XY%20Graph%20and%20Text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2310201%22%20slang%3D%22en-US%22%3EThanks%20Sergei.%20Why%20does%20having%20the%20text%20there%20cause%20the%20numbers%20to%20display%20so%20strangely%20e.g.%20up%20to%20250%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2310209%22%20slang%3D%22en-US%22%3ERe%3A%20Strange%20Behaviour%20With%20XY%20Graph%20and%20Text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2310209%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F569472%22%20target%3D%22_blank%22%3E%40CMSCMSCMS%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20x-values%20of%20an%20XY%20Scatter%20chart%20should%20not%20be%20text.%20Cell%20B191%20contains%20the%20text%20value%20N%2FA.%20This%20causes%20Excel%20to%20use%20sequential%20numbers%201%20...%20227%20for%20the%20x-axis.%3C%2FP%3E%0A%3CP%3EIf%20you%20either%20change%20B191%20to%20the%20error%20value%20%23N%2FA%20or%20clear%20this%20cell%2C%20the%20chart%20will%20look%20the%20way%20you%20want.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Chart.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277012i70B3061E4C3E37E7%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Chart.png%22%20alt%3D%22Chart.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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

@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

image.png

chart works correctly.

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

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

 

Chart.png

Thanks Hans, that explains it. I was trying to figure out where it got those values from.
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.

@JonPeltier 

Yes, that's what Sergei and I wrote.

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

@JonPeltier 

No problem!