Scatterpoot not using X values properly -- using relative ranking
in [Charts]
|
Prev: How do I change the color of the timeline boxes in my Ghant chart
Next: Chart moves on plot area when adding a third month
From: Eric_G on 11 Mar 2010 12:45 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
From: Bernard Liengme on 11 Mar 2010 13:39 If you have 3 columns (i.e. the first column has 1,2,3), then Excel will treat this as the x-values. To overcome this, select the x and y data before making the chart. If you have two columns, it sounds like you are making a Line chart because when Excel sees two ranges with numbers it uses both as data series and uses the ordinal values 1,2,3 ... for the category vales (x-values). This can be avoided by removing the title at the top of the x-values. Failing either of these, send me a sample file (get my email addy from my website) Tell me what version of Excel you are using best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Eric_G" <EricG(a)discussions.microsoft.com> wrote in message news:B48218C4-3AEB-4DE9-9198-7B4C946A38AD(a)microsoft.com... > 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
From: Tushar Mehta on 11 Mar 2010 13:44 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. On Thu, 11 Mar 2010 09:45:01 -0800, Eric_G <EricG(a)discussions.microsoft.com> wrote: >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
From: Jon Peltier on 11 Mar 2010 17:55 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 ------- Jon Peltier Peltier Technical Services, Inc. 774-275-0064 http://peltiertech.com/ On 3/11/2010 12:45 PM, Eric_G wrote: > 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
From: James Silverton on 12 Mar 2010 11:08
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 |