A LINEST function is a great tool that can be used in Excel or Google Sheets. With this function, we can perform linear regression analysis and also calculate various statistics that relate to a linear trendline that fits the best to a given set of data points.
This function will return the array of values. Each of these values presents different statistical information.
In the example below, we will show how this function works and how to use it in the example.
LINEST function explained
The syntax for the LINEST function is as follows:
|
1 |
=LINEST(known_ys, [known_xs], [const], [stats]) |
The explanation of every parameter is as follows:
- Known_y’s: This is a parameter that stores the array of a range of dependent variable data (Y-values) for which we aim to find the best-fit line. This parameter is mandatory.
- Known_x’s: This parameter is optional and represents the array of a range of independent variable data (X-values). If we do not provide it, the function will assume a series of sequential integers that will start from 1 as the X-values.
- Const: Another optional parameter specifies if we want to force the constant (Y-intercept) of the regression line through the origin (0). If we use TRUE or 1 we will define that the regression line will go through the origin. If we use FALSE or 0, the regression line will have a Y-intercept other than 0. If we do not insert any value, the default value is TRUE.
- Stats: Final optional argument which specifies if are there any additional statistics that can be included in the output. It is an array constant that we can use to extract the statistics like R-squared, standard errors, etc. If omitted, the function returns the slope and Y-intercept of the regression line.
To use the LINEST function, we need to keep in mind the following:
- We need to organize your data and have a set of data pairs (X, Y) in the columns next to each other.
- Our data ranges must be determined. Arrays for X-values and Y-values should be noted.
- Only when the first two are met, we can enter the LINEST formula.
- In the previous versions of Excel (versions older than Office 365 and non-online versions), we would need to press CTRL + SHIFT + ENTER to make this formula work, as it is an array formula. In newer versions, we do not have to do it.
How to use the LINEST function
For our example, we will make a list of data, consisting of the grades of several students in Mathematics and History:
Our goal is to find the relationship between Math and History grades and to check if there is any linear correlation between the two.
To achieve this, we will first calculate the average for each student across the two subjects (this is an option step, but we do it to simplify the dataset). For this, the new column Average will be added, and in that, the average of two grades will be calculated:
We will now use the LINEST function to perform the linear regression analysis and we will try to find the best-fit line for the relationship between Mathematics and History.
We will use the following formula and put it in cell E2:
|
1 |
=LINEST(C2:C6, B2:B6, TRUE, TRUE) |
and will get the following results:
In our formula, C2:C6 are History grades (dependent variable) and range B2:B6 represents the Mathematics grades (independent variable). Our third parameter will be TRUE to force the regression line through the origin (0), as well as the fourth one to include additional statistics like R-squared.
Since we use the 365 Office version, we do not have to press CTRL+SHIFT+ENTER to make the formula work. In older Office versions, we would have to do it.
The results that we got are an array containing the slope, Y-intercept, standard error of slope, standard error of Y-intercept, R-squared, and other requested statistics:
Looking at the column E, explanations are as follows:
- The Slope represents the change in the History grade for a one-unit increase in the Mathematics grade.
- Y-intercept gives us the expected History grade when the Mathematics grade is 0 (this one is not practical in our example).
- The R-squared value shows how well the linear regression line fits the data (higher values indicate a better fit).
The LINEST function in this example is used for a simple linear regression analysis that only has two variables. If you have more complex scenarios, you should consider multiple regression analysis or some other statistical methods.