Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

How does excel calculate the best fit line?

  1. Aug 14, 2012 #1
    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?
  2. jcsd
  3. Aug 14, 2012 #2

    Simon Bridge

    User Avatar
    Science Advisor
    Homework Helper

    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?
  4. Aug 14, 2012 #3
    Thank you for your reply, that may work.
  5. Aug 14, 2012 #4


    User Avatar
    Science Advisor

  6. Aug 15, 2012 #5

    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?
  7. Aug 15, 2012 #6

    Simon Bridge

    User Avatar
    Science Advisor
    Homework Helper

    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 impliment it.
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook