Thread: [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.

Originally Posted by FrCrilly

[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

Originally Posted by CaptainBlack

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.)

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.

Originally Posted by FrCrilly

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