How does excel calculate the best fit line?

[jsbxd9]

Background: I am working with pump curves in a hydraulic model. The model is written on excel. In the model pump curves are defined using tested flows vs. dynamic head (ft). The problem I am having is an iterative solution to fit an equation to the data points. Once the first equation is fit to the data points I skew them slightly to induce the R^2 value to 1. Each time I move a data point the best fit equation changes respectively with the order of the equation. The higher the order, the more accurate, hence the more the equation changes each time a data point is moved.

All in all I need to find how excel models the best fit line and comes up with the equation they use so I can experiment with a macro to do the iterative process. Can anyone help?

 

[Simon Bridge]

Welcome to PF;
Google for "least squares regression".

Am I reading this right:
You move data around to get a better fit?
If your data is not supposed to be a line, why not fit a different curve?

 

[jsbxd9]

Thank you for your reply, that may work.

 

[Bacle2]

There are goodness-of-fit tests used to see if a linear approximation makes sense:

http://en.wikipedia.org/wiki/Goodness_of_fit

 

[jsbxd9]

Simon:

When the data is moved around for all the points the 'new' best fit curve is automatically calculated with excel. That curve is then used, by me, to calculate the dynamic head by inputting the flow. The solution to the equation is then subtracted from the dynamic head that was changed to ensure the r^2 value is equivalent to 1. It is a way to check the accuracy of the curve.

I want to automate this system by using the area under the curve between each data point (dynamic head). When the data point is changed to make r^2 = 1 then the curve will slightly change. The next area calculated should use the new curve and make the data point equivalent to the respective curve. Iterating this a couple times throughout all data points should make an accurate curve.

Any suggestions?

 

[Simon Bridge]

You need to be able to describe the process in a series of steps in math.
Write out the algorithm ... use a math script program to implement it.