Scatterpoot not using X values properly -- using relative ranking

[Eric_G]

I have created a scatterplot where the X value is to be one value (risk) and
the Y value is to be another (return). I've entered the X and Y value ranges
properly and the y value is being plotted correctly, BUT the X value is not
being plotted as the correct X value. Instead, the X value is being
interpreted as the relative ranking in the list. In other words,

# in list X value Y Value
1 12.2 13.7
2 10.7 11.3
etc.

The graph is currently plotting the values as (1,13.7), then (2,11.3) and
NOT with the correct X values as listed in the chart. I've checked the
ranges and there's nothing indicating that the graph should look to the
"relative position" for the x-axis value. What gives?

thanks,
Eric

 

[Tushar Mehta]

One possibility is that the X values are not numeric as Excel sees
them. In an empty cell in the first row with data enter
=ISNUMBER({x}) where {x} is the cell containing the x value. Copy
this down to cover all the rows with data. All the results should be
TRUE.

Another possibility is that you are using the Line Chart, which looks
very much like a XY Scatter Chart but the x values are category
values. If so, make sure you are really using a XY Scatter Chart.

I have created a scatterplot where the X value is to be one value (risk) and
the Y value is to be another (return). I've entered the X and Y value ranges
properly and the y value is being plotted correctly, BUT the X value is not
being plotted as the correct X value. Instead, the X value is being
interpreted as the relative ranking in the list. In other words,

# in list X value Y Value
1 12.2 13.7
2 10.7 11.3
etc.

The graph is currently plotting the values as (1,13.7), then (2,11.3) and
NOT with the correct X values as listed in the chart. I've checked the
ranges and there's nothing indicating that the graph should look to the
"relative position" for the x-axis value. What gives?

thanks,
Eric

Regards,

Tushar Mehta
Microsoft MVP Excel 2000-present
www.tushar-mehta.com
Excel and PowerPoint tutorials and add-ins

 

[Jon Peltier]

Did you make a line chart? It will plot the data incorrectly.

Are the values in the X column really numeric, or are they text that
looks numeric? It only takes one text value to mess it up, and Excel
will convert the whole X range into 1, 2, 3, etc.

- Jon

 

[James Silverton]

Eric_G wrote on Thu, 11 Mar 2010 09:45:01 -0800:

# in list X value Y Value
1 12.2 13.7
2 10.7 11.3
etc.

The graph is currently plotting the values as (1,13.7), then
(2,11.3) and NOT with the correct X values as listed in the
chart. I've checked the ranges and there's nothing indicating
that the graph should look to the "relative position" for the
x-axis value. What gives?

I was initially intrigued by the term "scatterpoot" but I guess your
mail program, like mine, does not check Subject: lines

--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not