WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » LINEST Excel Function

LINEST Excel Function

LINEST Function in Excel

It is a built-in function in MS Excel. The LINEST function in Excel is used to calculate the statistics for a line. LINEST in excel uses the least-squares regression and returns an array that describes the straight line that best fits given data.

LINEST Formula in Excel

Below is the LINEST Formula in Excel.

LINEST Formula in Excel

The =Equation for the Line is:

y = mx + b

–or–

y = m1x1 + m2x2 + m3x3 + … + b

The LINEST in Excel has two arguments, out of which one is required. Where,

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course
  • known_y’s = It is a required parameter and indicates the set of y-values already known in the relationship y = mx + b.
    • If the range of known_y’s is in a single column, each column of known_x’s is interpreted as a separate variable.
    • If the range of known_y’s is in a single row, each row of known_x’s is interpreted as a separate variable.
  • known_x’s = It is an optional parameter and indicates the set of x-values that are already known in the relationship y = mx + b.
    • The range of known_x’s can have one or more sets of variables. If only one variable is used, known_y’s and known_x’s can be the ranges of any shape, if they have equal dimensions.
    • If more than one variable is used, known_y’s must be a vector (i.e., a range with a height of one row or width of one column).
  • const = It is an optional parameter and represents a logical value (TRUE/FALSE) specifying whether to force the constant b to be equal to 0.
    • If const is TRUE or skipped, b is calculated normally.
    • If const is FALSE, b is set equal to 0, and the m-values are adjusted to fit y = mx.
  • stats = It is an optional parameter and represents a logical value (TRUE/FALSE) specifying whether to return additional regression statistics.
    • If stats is TRUE, LINEST in excel returns the additional regression statistics; as a result, the returned array is {mn , mn-1,…,m1,b;sen,sen-1,…,se1,seb;r2,sey;F,df;ssreg,ssresid}.
    • If stats are FALSE or skipped, LINEST in Excel returns only the m-coefficients and the constant b.

How to Use the LINEST Function in Excel?

The said function is a Worksheet (WS) function. As a WS function, the LINEST function in excel can be entered as a part of the formula in a cell of a worksheet. Refer to a couple of examples given below to know more.

Let’s look at the examples given below. Each example covers a different use case implemented using the LINEST function in Excel.

You can download this LINEST Function Excel Template here – LINEST Function Excel Template

LINEST in Excel Example #1 – Slope

=LINEST (B2:B5, C2:C5, , FALSE)

As shown in the above formula, B2:B5 is the known-y’s, C2:C5 is known-x’s. 3rd parameter, i.e., const, is left blank, so it will be calculated. 4th parameter, i.e., stats, is marked FALSE.

LINEST Example 1

LINEST in Excel Example #2 – Simple Linear Regression

 = SUM (LINEST(B1:B6, A1:A6)*{9,1})

As shown in the above LINEST formula in excel, A1:A6 is the month number, and B2:B6 indicates the corresponding sales figures. So, based on the sales data of 6 months, sales data for the 9th month is to be estimated.

Things to Remember

  1. When entering an array constant (such as known_x’s) as an argument, commas can be used to separate values that are present in the same row, and semicolons can be used to separate the rows. Separator characters may vary depending on native regional settings.
  2. The y-values predicted by the regression equation may not be valid if they are outside the range of the y-values used to determine the equation.
  3. Formulas that return arrays must be entered as array formulas.
  4. When there’s only one independent x-variable, slope and y-intercept values can directly be calculated by using the following formulas:
    • Slope: =INDEX (LINEST (known_y’s, known_x’s),1)
    • Y-intercept: =INDEX (LINEST (known_y’s, known_x’s),2)
  5. A straight line can be described with slope and y-intercept:
    • Slope (m): To find the slope of a line, often represented as m: – For a line with two points (x1, y1) and (x2, y2); the slope is calculated as: m = (y2 – y1) / (x2 – x1).
    • Y-intercept (b): The y-intercept of a line, often represented as b, is the value of y at the point where the line crosses the y-axis.
    • The equation of a straight line is y = mx + b. Once the values of m and b are known, any point on the line can be calculated by putting the y- or x-value into the equation. See the TREND function in Excel.

LINEST Function in Excel Video

Recommended Articles

This has been a guide to LINEST Function in Excel. Here we discuss the LINEST Formula and how to use LINEST in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel

  • Quartile Deviation
  • Calculate Regression
  • Quartile Formula in Statistics
  • Regression and ANOVA
  • Arrays Excel VBA
  • FIND Excel
  • ROW Excel
  • QUARTILE Excel
  • MODE Excel
  • LARGE Excel
  • SMALL Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download LINEST Function Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More