LINEST
Formulas / LINESTCalculates the parameters of the best-fit line for a set of data points
=LINEST(known_ys, [known_xs], [const], [stats])
- known_y's - required, the set of y-values known in the relationship y = mx + b
- known_x's - [OPTIONAL] the set of x-values known in the relationship y = mx + b
- const - [OPTIONAL] a logical value that specifies whether to force the constant b to 0
- stats - [OPTIONAL] a logical value that tells LINEST if it should return additional regression statistics
Examples
=LINEST({1.8;5.3;8.2;12;13.5},{1;3;5;7;8})This example takes two sets of data and will return an array with two columns and one row containing the slope and the y-intercept of the regression line. The example will return an array of {1.6726,0.1317}. This means that the linear equation for the given data points is y = 1.6726x + 0.1317.
=LINEST({1.8;5.3;8.2;12;13.5},{1;3;5;7;8},TRUE,TRUE)You can also use the LINEST function to return additional statistics by using additional parameters. The example will return an array of five rows and two columns equal to {1.6726,0.1317; 0.0371,0.2017; 0.9985,0.2124; 2034.443,3; 91.7567,0.1353}.
Summary
The LINEST function is used to calculate a straight line using the "least squares" method.
- The LINEST function uses the "least squares" method to calculate the statistics of a best fit straight line through given y and x values.
- The LINEST function can return up to 10 separate statistics, including the slope, intercept, standard error, and more.
- By default, the LINEST function returns the slope and intercept. Additional statistics are returned when stats is set to TRUE.
Frequently Asked Questions
What is the LINEST function in Excel, and what is its purpose?
The LINEST function in Excel is used to perform a linear regression analysis on a set of data points and return information about the calculated regression line. It provides statistical information about the line of best fit, including the slope, intercept, and other statistical measures such as the coefficient of determination (R-squared).
Can the LINEST function handle multiple independent variables (multivariate regression)?
Yes, the LINEST function can handle multiple independent variables, making it suitable for multivariate regression. To perform multivariate regression, you need to provide a range of known_x's with multiple columns, each representing an independent variable.
How do I interpret the results of the LINEST function when the "stats" argument is set to TRUE?
When the "stats" argument is set to TRUE, LINEST returns an array of additional regression statistics.
Can the LINEST function be used for polynomial regression?
Yes, the LINEST function can be used for polynomial regression by creating additional columns of data that represent higher-order terms of the independent variable.
