The LINEST function returns more than one value at a time in an array. In its most basic form, LINEST returns just intercept and slope. Optionally, LINEST can also return 10 separate statistics for the regression analysis as shown in the worksheet above. In Excel 365, which supports dynamic arrays, the array of values will spill into cells in the worksheet automatically. In other versions of Excel, you must enter the LINEST as a multi-cell array formula to see all values.
Available Statistics
The table below shows the statistics that can be returned by the LINEST function. Note the first two, slope and intercept are returned by default. The other statistics are returned by setting the stats argument to TRUE. When all statistics are returned, they are delivered in a 2D array, 5 rows by 2 columns. In the worksheet shown above, the range F4:G8 shows the order in which statistics are returned.
Examples
By default, LINEST returns just two statistics, slope and intercept. For example: returns a 1 row by 2 column array like this: Setting the stats argument to TRUE or 1 will cause LINEST to return all 10 statistics: The result is an array with 5 rows and 2 columns: Note: values above have been rounded to make them easier to read.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
