{"id":31673,"date":"2023-10-27T23:05:36","date_gmt":"2023-10-27T21:05:36","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=31673"},"modified":"2024-04-02T21:31:13","modified_gmt":"2024-04-02T19:31:13","slug":"linest-function-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/linest-function-in-excel\/","title":{"rendered":"How to use LINEST function in Excel"},"content":{"rendered":"<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/6DC6B79E-38B7-4D4E-B955-891E85C35E72.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-31712\" title=\"How to use LINEST function in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/6DC6B79E-38B7-4D4E-B955-891E85C35E72-1024x576.png\" alt=\"How to use LINEST function in Excel\" width=\"1024\" height=\"576\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/6DC6B79E-38B7-4D4E-B955-891E85C35E72-1024x576.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/6DC6B79E-38B7-4D4E-B955-891E85C35E72-300x169.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/6DC6B79E-38B7-4D4E-B955-891E85C35E72-768x432.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/6DC6B79E-38B7-4D4E-B955-891E85C35E72.png 1280w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p>Excel has several built-in statistical functions that help analyze and summarize data. The LINEST function in <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/interquartile-range-in-excel\/\" target=\"_blank\" rel=\"noopener\">Excel is used to calculate<\/a> the statistics for a line that best fits your data. This is often referred to as linear regression or trendline analysis.<\/p>\n<p>In this article, we will cover the following in detail &#8211;<\/p>\n<h4>Download the Excel Workbook below to follow along and understand How to use the LINEST function in Excel \u2013<\/h4>\n<p><a class=\"IRPP_minimalist icon-download\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-Function.xlsx\" target=\"_blank\" rel=\"nofollow noopener\"><span class=\"ctaText px-3 text-capitalize\"><strong>download excel workbook<\/strong><\/span><span class=\"postTitle px-3\">LINEST-Function.xlsx<\/span><\/a><\/p>\n<p>&nbsp;<\/p>\n<h3><strong><span id=\"1\" style=\"color: #ff0000\">What is Linear Regression<\/span><\/strong><\/h3>\n<p>Before we dive into the LINEST function, let us first understand what is linear <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/regression-in-excel\/\" target=\"_blank\" rel=\"nofollow noopener\">regression<\/a>.<\/p>\n<p>Linear regression is a <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/descriptive-statistics-in-excel\/\" target=\"_blank\" rel=\"nofollow noopener\">statistical<\/a> method used to <strong>model the relationship between a dependent variable and one or more independent variables<\/strong> by fitting a linear equation to the observed data. The key assumption is that the <strong>relationship between the variables can be approximated by a straight line<\/strong>.<\/p>\n<p>The <strong>general form of a simple linear regression equation<\/strong> with one independent variable is:<\/p>\n<p>y = mx + b<\/p>\n<p>Here:<\/p>\n<ul>\n<li><strong>y<\/strong> is the dependent variable.<\/li>\n<li><strong>x<\/strong> is the independent variable.<\/li>\n<li><strong>m<\/strong> is the slope of the line, which represents the change in y for a unit change in x.<\/li>\n<li><strong>b<\/strong> is the y-intercept, the value of y when x is 0.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h3><strong><span id=\"2\" style=\"color: #ff0000\">Introduction to LINEST function<\/span><\/strong><\/h3>\n<p>The LINEST function in Excel is a <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/category\/formulas\/statistical\/\" target=\"_blank\" rel=\"nofollow noopener\">statistical tool<\/a> that employs the &#8220;least squares&#8221; method to compute the statistics for a line. It does this by determining a straight line that optimally fits your data, minimizing the sum of squared differences between observed and predicted values. The syntax of this function is as follows &#8211;<\/p>\n<p><strong>LINEST(known_y&#8217;s, [known_x&#8217;s], [const], [stats])<\/strong><\/p>\n<p>where,<\/p>\n<ul>\n<li><strong>known_y&#8217;s <\/strong>&#8211;\u00a0This is the array or range of dependent variables (y-values) in your data.<\/li>\n<li><strong>known_x&#8217;s<\/strong> &#8211; This is an optional argument. It is the array or range of independent variables (x-values) in your data. If omitted, Excel assumes the x-values are 1, 2, 3, &#8230;, n.<\/li>\n<li><strong>const<\/strong> &#8211; This is an optional argument.\n<ul>\n<li>If const is TRUE or omitted, the LINEST function includes an intercept in the model. The equation will be y = mx + b<\/li>\n<li>If const is FALSE, the function assumes there is no intercept. The equation will be y = mx.<\/li>\n<\/ul>\n<\/li>\n<li><strong>stats <\/strong>&#8211; This is an optional argument.\n<ul>\n<li>If stats is TRUE, the LINEST function returns additional regression statistics.<\/li>\n<li>If stats is FALSE or omitted, only the coefficients of the line are returned.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>Since this function returns an array of values, it enters to be entered as an array function as well. Please remember to press Ctrl +\u00a0 Shift\u00a0 + Enter after writing all the arguments of this function.<\/p>\n<p>&nbsp;<\/p>\n<h3><strong><span id=\"3\" style=\"color: #ff0000\">Example of LINEST function<\/span><\/strong><\/h3>\n<p>Who doesn&#8217;t love video games?!! Now we all know that <strong>more the hours put into a game, the higher will be the results<\/strong>. Your <strong>progress is tracked by your score, which directly corresponds to how much you practice<\/strong>.<\/p>\n<p>When linking two variables, regression analysis can be used to<strong> determine how much of an impact one variable has on the other<\/strong>. This means that regression can help us<strong> calculate how much your score will increase for each additional hour of gameplay<\/strong>. Let&#8217;s understand 2 foundational aspects of regression:<\/p>\n<p>y = mx + b<\/p>\n<ul>\n<li>y &#8211; It is the score you get.<\/li>\n<li>x &#8211; It is the number of hours you put in.<\/li>\n<li>b &#8211; It is the intercept. Picture it as a starting point. Even if you didn&#8217;t play at all, you might still have a base score.<\/li>\n<li>m &#8211; It is the slope. It tells you how much your score changes for each hour you play. If the slope is high, your score jumps a lot with each hour. If it&#8217;s low, the increase is smaller.<\/li>\n<\/ul>\n<p>Here is the data set for this example &#8211;<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31679\" title=\"How to use LINEST function in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-1.png\" alt=\"How to use LINEST function in Excel\" width=\"650\" height=\"265\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-1.png 650w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-1-300x122.png 300w\" sizes=\"(max-width: 650px) 100vw, 650px\" \/><\/a><\/p>\n<p>Follow the steps below to use the LINEST function to get the slope and intercept for this example &#8211;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 1:<\/strong> <\/span> Enter the LINEST function.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-2.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31680\" title=\"How to use LINEST function in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-2.png\" alt=\"How to use LINEST function in Excel\" width=\"741\" height=\"371\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-2.png 741w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-2-300x150.png 300w\" sizes=\"(max-width: 741px) 100vw, 741px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 2:<\/strong> <\/span> Enter the first argument i.e. known_y&#8217;s. Here, is the list containing the scores.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-3.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31681\" title=\"How to use LINEST function in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-3.png\" alt=\"How to use LINEST function in Excel\" width=\"742\" height=\"354\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-3.png 742w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-3-300x143.png 300w\" sizes=\"(max-width: 742px) 100vw, 742px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 3:<\/strong> <\/span> Enter the second argument i.e. known_x&#8217;s. Here, is the list containing the hours played.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-4.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31682\" title=\"How to use LINEST function in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-4.png\" alt=\"How to use LINEST function in Excel\" width=\"739\" height=\"355\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-4.png 739w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-4-300x144.png 300w\" sizes=\"(max-width: 739px) 100vw, 739px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 4:<\/strong> <\/span>Press Ctrl + Shift + Enter.<\/p>\n<p>You will see that Excel will provide you with two values. The first one is the slope i.e. 7.3 and the second is the intercept i.e. 49.8.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-5.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31677\" title=\"How to use LINEST function in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-5.png\" alt=\"How to use LINEST function in Excel\" width=\"740\" height=\"335\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-5.png 740w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-5-300x136.png 300w\" sizes=\"(max-width: 740px) 100vw, 740px\" \/><\/a><\/p>\n<p>The equation that will be formed using these values is &#8211;<\/p>\n<p><strong>y = 7.3x + 49.8<\/strong><\/p>\n<p>Now, let us add another row with the <strong>number of hours played as 7<\/strong> and use this equation to get the probable score.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-6.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31683\" title=\"How to use LINEST function in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-6.png\" alt=\"How to use LINEST function in Excel\" width=\"738\" height=\"273\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-6.png 738w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/LINEST-in-Excel-6-300x111.png 300w\" sizes=\"(max-width: 738px) 100vw, 738px\" \/><\/a><\/p>\n<p>The score will be around 101.<\/p>\n<p>&nbsp;<\/p>\n<h3><strong><span id=\"4\" style=\"color: #ff0000\">Conclusion<\/span><\/strong><\/h3>\n<p>The article discusses the use of the LINEST function in Excel for linear regression analysis. Linear regression is explained as a statistical method to model the relationship between a dependent variable and one or more independent variables by fitting a linear equation to observed data. The LINEST function, employing the least squares method, calculates the statistics for a line that best fits the data and returns an array describing the line, including slope and intercept.<\/p>\n<p><a href=\"https:\/\/support.microsoft.com\/en-us\/office\/linest-function-84d7d0d9-6e50-4101-977a-fa7abf772b6d#:~:text=Description,array%20that%20describes%20the%20line.\" target=\"_blank\" rel=\"nofollow noopener\"><strong>Click here<\/strong><\/a> to learn more about the LINEST function.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Excel has several built-in statistical functions that help analyze and summarize data. The LINEST function in Excel is used to calculate the statistics for a line that best fits your data. This is often referred to as linear regression or trendline analysis. In this article, we will cover the following in detail &#8211; Download the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":31712,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[499],"tags":[1422,1423,1424],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/31673"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=31673"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/31673\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/31712"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=31673"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=31673"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=31673"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}