+ Reply to Thread
Results 1 to 7 of 7

How to get intermediate values from smooth graph in Excel ?

  1. #1
    Tushar
    Guest

    How to get intermediate values from smooth graph in Excel ?

    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 ?

  2. #2
    Jon Peltier
    Guest

    Re: How to get intermediate values from smooth graph in Excel ?

    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 ?




  3. #3
    Bernard Liengme
    Guest

    Re: How to get intermediate values from smooth graph in Excel ?

    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 ?




  4. #4
    steve
    Guest

    RE: How to get intermediate values from smooth graph in Excel ?

    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 ?


  5. #5
    steve
    Guest

    Re: How to get intermediate values from smooth graph in Excel ?

    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 ?

    >
    >
    >


  6. #6
    Jerry W. Lewis
    Guest

    RE: How to get intermediate values from smooth graph in Excel ?

    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 ?


  7. #7
    Jerry W. Lewis
    Guest

    RE: How to get intermediate values from smooth graph in Excel ?

    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 ?


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1