Excel 2007 : X-Y graph

[kenppy]

I have two cols of results which I want to graph as X against Y.
Graph with x and y as the axes NOT chart which is all that there appears to be on offer with excel.
I hope this is clear, I want to graph x to y which gives a single line, not chart which gives two lines.
thanks

[zbor]

Select data and use Scatter graph

[kenppy]

a scatter graph is still the same thing, two series.
I want to plot x against y, x on the hor axis y on the vert giving ONE line joining the points

[zbor]

In that case you did something wrong.
Try again.

Select both X and Y data
Insert -> Scatter with Smooth lines
OK

[kenppy]

thanks
That's it, it was the smooth lines that reduced the data to one curve.

Now what I have is a curve that goes through the points.

I'm trying to do something this way because there is no formula linking x to y.

Now I want to read off a value for y from a given x. This is somewhere on the curve between the points. How do I extrapolate this value?

[zbor]

Right click on your curve.
Add Trendline.
Check Display Equation on chart and choose best fitted curve from the Type above

[kenppy]

Thanks, looks like just the job; just one more question is there some automatic way I can get an equation like this into a formula?

y = 0.3583x5 - 6.375x4 + 42.125x3 - 127.12x2 + 173.02x - 80

[shg]

You can get the coefficients directly on the worksheet.

Select, say, A1:F1, paste this in the formula bar, and confirm with Ctrl+Shift+Enter rather than just Enter:

=LINEST(yRange, xRange^{1,2,3,4,5,6})

Then to reconstruct the fit from the coefficients

=SERIESSUM(xValue, 5, -1, $A$1:$E$1) + $F$1

[TMS]

I think the first formula needs a curly bracket:

=LINEST(yRange, xRange^{1,2,3,4,5,6})

=LINEST(C4:C8, B4:B8^{1,2,3,4,5,6})


Regards

[shg]

Corrected, thanks TMS.

[kenppy]

If any member's response has helped to solve your problem, please use the scales icon on the top right of their post to show your appreciation

sorry guys I cannot see this icon anywhere in the post

[zbor]

Look here:

[kenppy]

i found this now that you pointed me to SERIESSUM

How to use the SERIESUM() function:

Type "=SERIESSUM(".
Enter the coordinate of the "x" value "1"
Type a comma.
Enter the coordinate of the "n" value "1".
Tyep a comma.
Enter the coordinate for the "m" value "1".
Type a comma.
Enter the coordinates for the range of "coefficients", "C2:C4".
Type ")" then press the "Enter" key

this is my data
10000 50
50000 200
200000 1800
350000 3500

and this is the formula from a 3rd order poly
y = -1E-13x3 + 6E-08x2 + 0.0003x + 40.755

Does the above mean that I have to type the coords manually? I was hoping that the formula could be inserted such that if the data was tweaked this would be updated behind the scenes.

[MrShorty]

There's no reason I can see why this can't be set up to automatically update. As shg showed you, use the LINEST function to generate the polynomial coefficients from the raw data. Then use the SERIESSUM function to find y at some arbitrary x. If the "coefficients" argument is pointing to the cells with the LINEST output, then any change in the raw data should cause the spreadsheet to update automatically. The "x", "n", and "m" arguments can also be references to other cells, if you want to be readily able to change those values.