For example:
x y
1 5.5
2 8.5
3 10.5
4 11.5
For x=1.56, what will be the corresponding value of y from drawn graph ?
For example:
x y
1 5.5
2 8.5
3 10.5
4 11.5
For x=1.56, what will be the corresponding value of y from drawn graph ?
Check this post in the archives:
http://www.mcse.ms/archive144-2004-1-346324.html
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
"Tushar" <[email protected]> wrote in message
news:[email protected]...
> For example:
> x y
> 1 5.5
> 2 8.5
> 3 10.5
> 4 11.5
> For x=1.56, what will be the corresponding value of y from drawn graph ?
You could get an approximate value using interpolation: this imagines a
straight line drawn thru two points that encompass you x value
The slope of the line joining x=1 and x=2 is (8.5-5.5)/(2-1) = 3
So y =3x+c
The line goes thru the point (1, 5.5) so 5.5 = 3*1+c hence c = 2.5
For x=1.56; y=3*1.56 + 2.5 = 7.81
For a better approximation insert a trendline on the chart (use Help and
then return here with questions)
I fitted a second order polynomial (a quadratic) and got y =-0.5x^2 + 4.5x +
1/5 with a R2 value of 1 (a very good fit), I used LINEST (see
http://www.stfx.ca/people/bliengme/E...Polynomial.htm)
to get these values into cells on the worksheet. When I use x=1.56 my
y-value is 7.3032
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"Tushar" <[email protected]> wrote in message
news:[email protected]...
> For example:
> x y
> 1 5.5
> 2 8.5
> 3 10.5
> 4 11.5
> For x=1.56, what will be the corresponding value of y from drawn graph ?
If the points are connected linearly with no smoothing, would not f(x) at x =
1.56 be equal to 7.18.
This is based on the interpolation formula:
(where x[1], y[1] are the known values immediately preceding the
interpolation; x[2], y[2] are the known values immediately following the
interpolation; x[i], y[i] are the values at the point of the interpolation)
Starting with the interpolation formula:
y[i] - y[1] x[i] - x[1]
----------- = ------------
y[2] - y [1] y[i] - y[1]
From which we get:
y[i] = (x[i] - x[1])(y[2] - y[1])
----------------------------- + y[1]
x[2] - x[1]
Substituting in the values for x = 1.56 we have
(1.56 - 1)(8.5 - 5.5)
y[i] = ----------------------- + 5.5 = 7.18
2 - 1
"Tushar" wrote:
> For example:
> x y
> 1 5.5
> 2 8.5
> 3 10.5
> 4 11.5
> For x=1.56, what will be the corresponding value of y from drawn graph ?
I think you may have made a typo on your linear interpolation, Berrnard. I
believe that you meant y = 3*1.56 + 2.5 = 7.18
Steve
"Bernard Liengme" wrote:
> You could get an approximate value using interpolation: this imagines a
> straight line drawn thru two points that encompass you x value
> The slope of the line joining x=1 and x=2 is (8.5-5.5)/(2-1) = 3
> So y =3x+c
> The line goes thru the point (1, 5.5) so 5.5 = 3*1+c hence c = 2.5
> For x=1.56; y=3*1.56 + 2.5 = 7.81
>
> For a better approximation insert a trendline on the chart (use Help and
> then return here with questions)
> I fitted a second order polynomial (a quadratic) and got y =-0.5x^2 + 4.5x +
> 1/5 with a R2 value of 1 (a very good fit), I used LINEST (see
> http://www.stfx.ca/people/bliengme/E...Polynomial.htm)
> to get these values into cells on the worksheet. When I use x=1.56 my
> y-value is 7.3032
>
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Tushar" <[email protected]> wrote in message
> news:[email protected]...
> > For example:
> > x y
> > 1 5.5
> > 2 8.5
> > 3 10.5
> > 4 11.5
> > For x=1.56, what will be the corresponding value of y from drawn graph ?
>
>
>
Tushar, you specifically asked about how Excel would draw it, which I
addressed in my previous post. If you are more generally interested in in
different methods of interpolation, then there are several reasonable
approached:
linear: 7.18 (as noted by several respondants)
Bezier: 7.248992 (as noted in my previous post)
cubic spline: 7.2568768
http://groups.google.com/group/micro...2966520eccdb1f
polynomial fit (order >=2): 7.3032 =TREND(ydata,xdata^{1,2},1.56^{1,2})
since the posted observations exactly fit 1.5+4.5*x-x^2/2
rational linear: 7.34210526315789 from fitting the monotonic function
y=(a+b*x)/(1+c*x)
Jerry
"Jerry W. Lewis" wrote:
> Brian Murphy has shown that the Excel chart smoother appears to use Bezier
> curves. You can plug your values directly into his example file
> http://www.xlrotor.com/Smooth_curve_...ample_file.zip
> to get 7.248992 as the value interpolated by the chart smoother at 1.56
>
> Jerry
>
> "Tushar" wrote:
>
> > For example:
> > x y
> > 1 5.5
> > 2 8.5
> > 3 10.5
> > 4 11.5
> > For x=1.56, what will be the corresponding value of y from drawn graph ?
Brian Murphy has shown that the Excel chart smoother appears to use Bezier
curves. You can plug your values directly into his example file
http://www.xlrotor.com/Smooth_curve_...ample_file.zip
to get 7.248992 as the value interpolated by the chart smoother at 1.56
Jerry
"Tushar" wrote:
> For example:
> x y
> 1 5.5
> 2 8.5
> 3 10.5
> 4 11.5
> For x=1.56, what will be the corresponding value of y from drawn graph ?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks