Excel LINEST Function

Excel LINEST Function

 
When you have data points given and you like to get the trend of these points, it is necessary to learn Excel LINEST function. It returns statistics that describe a linear trend matching known data points, by fitting a straight line using the least squares method.

Description of Example
The example shows how to get coefficients of a polynomial given 4 data points. We will use LINEST function to fit the points in a curve with a single equation.
Excel Linest Function
 

Evaluation of Formula
LINEST function works when there are known x values and y values. In this example, our known y values are given in ranges C6 to C9, while x values are in ranges B6 o B9. But because we are trying to get the coefficients of cubic polynomial, we need to enter the x values with superscript array from 1 to 3. Array can be distinguished by enclosed brackets '{ }'.
This formula will output 4 values, A, B, C and D, which is why we need to select 4 cells in a row (ranges E10 to H10 in below example) and then press Ctrl + Shift + Enter.
 
Excel Linest Function 2
 

Output
The LINEST formula will return 4 values as expected and the unknown equation can be written as y = 2x3 + 3x2 - 6x + 8.
 
Excel Linest Function 3
 
 
 

Did you find this content useful? If yes, please consider helping us by making a Donation. You can get free pdf when you donate now, see details here.