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

Line of Best fit in EXCEL ?

  1. Sep 25, 2004 #1
    :confused: I have plotted a few points (8 infact ) in Excel and would like to acquire the equation for the line of best fit. How do I do this using the computer or do I have to do it by hand?
  2. jcsd
  3. Sep 25, 2004 #2

    Dr Transport

    User Avatar
    Science Advisor
    Gold Member

    You can either do a "least squares" fit to what ever order of polynomial you want to use. In Excel, plot the data points, then you can fit the data points to any order of polynomial up to 6th order, exponential curve and a few more I ccannot remember. The correleation coefficient is diplayed on the polt, so you can determine the best equation from that.
  4. Sep 25, 2004 #3


    User Avatar

    Staff: Mentor

    Adding a trend line is easy through the chart properties, but fitting an equation is a little tougher (unless you do it manually by looking at the fitted trend-line). IIRC, it requries an add-in that is not installed automatically. In the tools menu, click "add-ins" and I think the "Solver add-in" is the one you need.
  5. Sep 25, 2004 #4


    User Avatar
    Science Advisor
    Homework Helper
    Gold Member

    In Excel, when viewing an XY-scatter chart look for the "Chart" menu and select "Add Trendline". To get the equation of the line, you have check the box in the Options tab of "Add Trendline".
  6. Sep 25, 2004 #5


    User Avatar
    Staff Emeritus
    Science Advisor
    Gold Member

    The excel command you are looking for is

    LINEST(yrange,xrange, b, stats)

    for the x and y ranges enter the row column information for your data for example if your data is in the first 8 cells of the first 2 columns you would enter a1:a8 for the xrange and b1:b8 for the y range. The b and stats parameters are logical (b=true if you want Excel to compute the y intercept, b=false of you want it to be 0) The last gives more stats if you set it to true.

    You need to select a 3x2 block of cells type = in the command line, enter the formula then type ctrl+Shift+enter to enter the Array formula.

    You would do well to get into the Excel help system and research array formulas and the Linest command.
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook