- #1
JamesJames
- 205
- 0
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?How can I use Excel to find the line of best fit for a set of data points?
In summary, you can use the LINEST command in Excel to fit a line to data points. You need to select a 3x2 block of cells and enter the formula then type ctrl+shift+enter to enter the Array formula.
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
- 2,606
- 800
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.
- #3
russ_watters
Mentor
- 23,161
- 10,367
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.
- #4
- 6,876
- 2,310
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".
- #5
Integral
Staff Emeritus
Science Advisor
Gold Member
- 7,255
- 66
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.
https://www.extendoffice.com/documents/excel/2642-excel-best-fit-line-curve-function.html
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.
https://www.extendoffice.com/documents/excel/2642-excel-best-fit-line-curve-function.html
Last edited by a moderator:
1. What is a line of best fit in Excel?
A line of best fit in Excel is a straight line that represents the general trend of a set of data points. It is used to show the relationship between two variables and to make predictions.
2. How do I create a line of best fit in Excel?
To create a line of best fit in Excel, you first need to plot your data points on a scatter plot. Then, right-click on one of the data points and select "Add Trendline." Choose the type of trendline you want (linear, exponential, etc.) and check the box for "Display equation on chart" to see the equation for the line of best fit.
3. Can I customize the line of best fit in Excel?
Yes, you can customize the line of best fit in Excel by changing its color, width, and style. You can also change the type of trendline or add additional data points to the chart. Simply right-click on the trendline and select "Format Trendline" to make these changes.
4. How do I interpret the equation for the line of best fit in Excel?
The equation for the line of best fit in Excel is in the form of y = mx + b, where m is the slope of the line and b is the y-intercept. The slope represents the rate of change between the two variables, while the y-intercept is the value of y when x is equal to 0. This equation can be used to make predictions or to determine the relationship between the two variables.
5. Can I add a line of best fit to an existing chart in Excel?
Yes, you can add a line of best fit to an existing chart in Excel by selecting the chart and going to the "Design" tab. Then, click on "Add Chart Element" and select "Trendline." You can also format the trendline by right-clicking on it and selecting "Format Trendline."
Similar threads
-
Computing and Technology
-
Set Theory, Logic, Probability, Statistics
-
Computing and Technology
-
Set Theory, Logic, Probability, Statistics
-
Set Theory, Logic, Probability, Statistics
-
Set Theory, Logic, Probability, Statistics
-
Set Theory, Logic, Probability, Statistics
-
Set Theory, Logic, Probability, Statistics
-
STEM Educators and Teaching
-
Programming and Computer Science