[In short, how do I calculate accurate meaningful values for A,B and C when fitting a curve to real data using excel?]
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.