Excel LINEST Function
If you want to follow along with this tutorial, please download the example spreadsheet.
Description
The Excel LINEST function returns the statistic for a best fit straight line based on the supplied set of x value and y value by using the “least squares” method. The function returns an array of constant values.
The equation for the line is
y = mx + b
where
x is the independent variable;
y is the dependent variable;
m is the slope (gradient) of the line;
b is a constant, equal to the value of y when x = 0
or
y = m1x1+m2x2+…+b
where
the x's are the independent variable ranges;
y is the dependent variable;
the m's are constant multipliers for each x range;
b is a constant.
Syntax and arguments
Formula syntax
Arguments
|
Return Value
The LINEST function returns an array of values.
Remark
The LINEST function returns an array, you must enter it as a multi-cell array formula and press CTRL + Shift + Enter keys to get the results.
Version
Excel 2003 or later
Usage and Examples
Example 1: for one set of known_xs, and ignore const and stats
In the list B4:B8, contains the known_xs, in the list C4:C8, contains the know_ys, now select cells E4:F4, then type below formula to the formula bar:
=LINEST(C4:C8,B4:B8)
Press Ctrl + Shift + Enter key, the array of results gets out.
Example 2: for two or more sets of known_xs and const and stats are TRUEs
In the list B11:D15, contains the known_xs, in the list E11:E15, contains the know_ys, const and stats are TRUEs, select range G11:J15, then type below formula to the formula bar:
=LINEST(E11:E15,B11:D15,TRUE,TRUE)
Press Ctrl + Shift + Enter key, the array of results gets out.
Relative Functions:
-
Excel ROUNDOWN Function
The ROUNDDOWN function rounds a number down (toward zero) to a specified number of digits.
-
Excel RANDBETWEEN Function
The RANDBETWEEN function returns a random number between two given numbers.
-
Excel ROMAN Function
The ROMAN function converts a number to a roman number in text.
-
Excel ROUND Function
The Excel ROUND function rounds a number to a specified number of digits.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
