different statistical information<\/strong>.<\/p>\n\n\n\nIn the example below, we will show how this function works and how to use it in the example.<\/p>\n\n\n\n
LINEST function explained<\/h2>\n\n\n\n The syntax for the LINEST function is as follows:<\/p>\n\n\n\n
=LINEST(known_ys, [known_xs], [const], [stats])<\/code><\/pre>\n\n\n\nThe explanation of every parameter is as follows:<\/p>\n\n\n\n
\nKnown_y\u2019s:<\/strong> This is a parameter that stores the array of a range of dependent variable data (Y-values)<\/strong> for which we aim to find the best-fit line. This parameter is mandatory<\/strong>.<\/li>\n\n\n\nKnown_x\u2019s:<\/strong> This parameter is optional and represents the array of a range of independent variable data (X-values)<\/strong>. If we do not provide it, the function will assume a series of sequential integers that will start from 1 as the X-values<\/strong>.<\/li>\n\n\n\nConst: <\/strong>Another optional parameter specifies if we want to force the constant (Y-intercept)<\/strong> of the regression line through the origin (0)<\/strong>. If we use TRUE or 1<\/strong> we will define that the regression line will go through the origin. If we use FALSE or 0<\/strong>, the regression line will have a Y-intercept other than 0<\/strong>. If we do not insert any value, the default value is TRUE<\/strong>.<\/li>\n\n\n\nStats:<\/strong> Final optional argument which specifies if are there any additional statistics<\/strong> 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<\/strong>. If omitted, the function returns the slope and Y-intercept<\/strong> of the regression line.<\/li>\n<\/ul>\n\n\n\nTo use the LINEST function<\/strong>, we need to keep in mind the following:<\/p>\n\n\n\n\nWe need to organize your data and have a set of data pairs (X, Y)<\/strong> in the columns next to each other.<\/li>\n\n\n\nOur data ranges must be determined. Arrays for X-values and Y-values should<\/strong> be noted.<\/li>\n\n\n\nOnly when the first two are met, we can enter the LINEST formula<\/strong>.<\/li>\n\n\n\nIn the previous versions of Excel (versions older than Office 365 and non-online versions<\/strong>), we would need to press CTRL + SHIFT + ENTER<\/strong> to make this formula work, as it is an array formula. In newer versions, we do not have to do it.<\/li>\n<\/ol>\n\n\n\nHow to use the LINEST function<\/h2>\n\n\n\n For our example, we will make a list of data, consisting of the grades of several students in Mathematics and History<\/strong>:<\/p>\n\n\n\n <\/figure>\n\n\n\nOur goal is to find the relationship between Math and History grades<\/strong> and to check if there is any linear correlation<\/strong> between the two.<\/p>\n\n\n\nTo achieve this, we will first calculate the average<\/strong> for each student across the two subjects (this is an option step, but we do it to simplify the dataset)<\/strong>. For this, the new column Average<\/strong> will be added, and in that, the average of two grades will be calculated:<\/p>\n\n\n\n <\/figure>\n\n\n\nWe will now use the LINEST function<\/strong> to perform the linear regression analysis and we will try to find the best-fit line<\/strong> for the relationship between Mathematics and History.<\/p>\n\n\n\nWe will use the following formula and put it in cell E2<\/strong>:<\/p>\n\n\n\n=LINEST(C2:C6, B2:B6, TRUE, TRUE)<\/code><\/pre>\n\n\n\nand will get the following results:<\/p>\n\n\n\n <\/figure>\n\n\n\nIn our formula, C2:C6<\/strong> are History grades (dependent variable)<\/strong> and range B2:B6<\/strong> represents the Mathematics grades (independent variable).<\/strong> Our third parameter will be TRUE<\/strong> to force the regression line through the origin (0)<\/strong>, as well as the fourth one to include additional statistics like R-squared<\/strong>.<\/p>\n\n\n\nSince we use the 365 Office version, we do not have to press CTRL+SHIFT+ENTER<\/strong> to make the formula work. In older Office versions, we would have to do it.<\/p>\n\n\n\nThe 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<\/strong>:<\/p>\n\n\n\n <\/figure>\n\n\n\nLooking at the column E<\/strong>, explanations are as follows:<\/p>\n\n\n\n\nThe Slope represents the change<\/strong> in the History grade for a one-unit increase<\/strong> in the Mathematics grade.<\/li>\n\n\n\nY-intercept<\/strong> gives us the expected History grade<\/strong> when the Mathematics grade is<\/strong> 0 (this one is not practical in our example).<\/li>\n\n\n\nThe R-squared value<\/strong> shows how well the linear regression line fits the data<\/strong> (higher values indicate a better fit).<\/li>\n<\/ul>\n\n\n\nThe LINEST function<\/strong> in this example is used for a simple linear regression analysis<\/strong> that only has two variables<\/strong>. If you have more complex scenarios, you should consider multiple regression analysis or some other statistical methods.<\/p>\n","protected":false},"excerpt":{"rendered":"A LINEST function is a great tool that can be used in Excel or Google Sheets. With this function, we can perform linear…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23],"tags":[170],"yoast_head":"\n
Using the LINEST function<\/title>\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\t \n\t \n\t \n