Results 1 to 5 of 5

Math Help - [Req] Curve Fitting using Excel

  1. #1
    Newbie
    Joined
    Dec 2007
    Posts
    3

    [Req] Curve Fitting using Excel

    [In short, how do I calculate accurate meaningful values for A,B and C when fitting a curve to real data using excel?]

    Hi All,

    Just a business math excel problem that I would appreciate some help with.

    To explain my query, I have 3 seperate figures on an excel sheet which I want to curve fit with one formula (I will call the 3 figures the observed). I then apply an equation of a curve to these figures (under Ax^2+Bx+C which produces figures which I will call the expected). I now want to generate values of A, B and C so that the expected values will match the observed values as closely as possible. I do this by first taking the difference between the observed and expected values, and then setting an equation =var(sum(O-E))+stdev(sum(O-E)). I then go to the solver function of excel and ask it to set this equations value to 0 by changing values A,B and C.

    While solver does produce a curve that graphically is a match, its clear that the values produced for A and B are meaningless. A turns out to be a tiny negative error term regardless of whether the curve is u or n shaped and B is always approximately =1.

    While I have been experimenting with solver options, I still cannot produce meaningful figures. Can anyone advise me on how to produce meaningful values for A,B (and C), preferably in excel either by solver, macro or another function.

    I hope the above is clear. All help will be greatly appreciated. Thanks In Advance for all responses.
    Last edited by FrCrilly; December 26th 2007 at 09:32 AM.
    Follow Math Help Forum on Facebook and Google+

  2. #2
    Grand Panjandrum
    Joined
    Nov 2005
    From
    someplace
    Posts
    14,972
    Thanks
    4
    Quote Originally Posted by FrCrilly View Post
    [In short, how do I calculate accurate meaningful values for A,B and C when fitting a curve to real data using excel?]

    Hi All,

    Just a business math excel problem that I would appreciate some help with.

    To explain my query, I have 3 seperate figures on an excel sheet which I want to curve fit with one formula (I will call the 3 figures the observed). I then apply an equation of a curve to these figures (under Ax^2+Bx+C which produces figures which I will call the expected). I now want to generate values of A, B and C so that the expected values will match the observed values as closely as possible. I do this by first taking the difference between the observed and expected values, and then setting an equation =var(sum(O-E))+stdev(sum(O-E)). I then go to the solver function of excel and ask it to set this equations value to 0 by changing values A,B and C.

    While solver does produce a curve that graphically is a match, its clear that the values produced for A and B are meaningless. A turns out to be a tiny negative error term regardless of whether the curve is u or n shaped and B is always approximately =1.

    While I have been experimenting with solver options, I still cannot produce meaningful figures. Can anyone advise me on how to produce meaningful values for A,B (and C), preferably in excel either by solver, macro or another function.

    I hope the above is clear. All help will be greatly appreciated. Thanks In Advance for all responses.
    Sorry but what do you mean by "menaingfull"? Does the curve found by
    solver fit the data?

    Also fitting a quadratic to 3 points is not advisable unless you know that
    the data is "noise free" i.e. exact. Otherwise the curve may well do anything
    between the data points.

    RonL
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Newbie
    Joined
    Dec 2007
    Posts
    3
    Quote Originally Posted by CaptainBlack View Post
    Sorry but what do you mean by "menaingfull"? Does the curve found by
    solver fit the data?

    Also fitting a quadratic to 3 points is not advisable unless you know that
    the data is "noise free" i.e. exact. Otherwise the curve may well do anything
    between the data points.

    RonL
    The 3 data points are exact as they are stock prices over a certain 3 consecutive days (in the past). I want to curve fit to these 3 data points and then d2y/dx2 the curve to calculate the rate of change in the stock price over the 3 days. The value of A I'm producing at present is meaningless as it's a small negative error term, regardless of whether the curve is U shaped or N shaped. (should be positive and negative respectively). Apologies if not clear.

    (Unable to attach an excel file at present. Will come back and attach one in a few days.)
    Follow Math Help Forum on Facebook and Google+

  4. #4
    MHF Contributor
    Joined
    Aug 2007
    From
    USA
    Posts
    3,110
    Thanks
    2
    Didn't we just do this one somewhere else?

    It also will REALLY help if your three points are NOT colinear. If they are colinear, you WILL NOT be happy with the coefficient on x^2, that is, if you insist on a quadratic collocation.

    If you notice it is linear and give up on the quadratic, that will be nice, but will not help much with a nonzero second deriviative.
    Follow Math Help Forum on Facebook and Google+

  5. #5
    Grand Panjandrum
    Joined
    Nov 2005
    From
    someplace
    Posts
    14,972
    Thanks
    4
    Quote Originally Posted by FrCrilly View Post
    The 3 data points are exact as they are stock prices over a certain 3 consecutive days (in the past). I want to curve fit to these 3 data points and then d2y/dx2 the curve to calculate the rate of change in the stock price over the 3 days. The value of A I'm producing at present is meaningless as it's a small negative error term, regardless of whether the curve is U shaped or N shaped. (should be positive and negative respectively). Apologies if not clear.
    Fitting 3 points to a quadratic will almost always produce a curve which
    passes exactly through the points. There should be negligable to zero
    error.

    (Unable to attach an excel file at present. Will come back and attach one in a few days.)
    You will probably get only nonsense out of the process. Stock prices are not
    error free they represent if anything an underlying behaviour with sampling
    noise of some type superinposed.

    RonL
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. Fitting curve Excel (XLfit)
    Posted in the Advanced Statistics Forum
    Replies: 1
    Last Post: January 26th 2011, 04:13 PM
  2. Curve Fitting
    Posted in the Advanced Algebra Forum
    Replies: 11
    Last Post: November 23rd 2010, 03:55 PM
  3. Curve Fitting 2
    Posted in the Calculus Forum
    Replies: 8
    Last Post: September 15th 2010, 06:38 AM
  4. Curve fitting
    Posted in the Math Software Forum
    Replies: 7
    Last Post: November 27th 2008, 04:21 AM
  5. Replies: 2
    Last Post: December 28th 2007, 02:48 PM

Search Tags


/mathhelpforum @mathhelpforum