Missing values ​​in MS Excel LINEST, TREND, LOGEST and GROWTH functions

It turns out to be "trivial" if you know the trick.

To use LINEST with missing values, you need to create an X-matrix (r rows by columns c) and a Y-vector (r rows by one column) as usual. You also need to create an additional column in the X-matrix that will serve as the indicator variable. Place this column immediately to the left of matrix X. So, if the X matrix starts in column B, place an additional column in column A. Set this indicator value to zero for each row you want to skip. Set this value to one for each line you want to include. Multiply each column of the X-matrix and Y-vector by this indicator variable. Place this new expanded X matrix and new Y vector elsewhere in the spreadsheet.You should now have a new X-matrix (r rows by c + 1 columns) and a Y-vector with rows of zeros straight across for each row to be omitted. THIS IS CRITICAL!

Now use the LINEST function as usual, specifying the entire Y-vector and the extended r × (c + 1) X-matrix (with indicator column included as the first two function parameters, "False" (that is, zero) as the third parameter and "TRUE" (that is, one) or "FALSE" (that is, zero) as the fourth parameter of the function. Correct parameter estimates are displayed in the first line of LINEST output. The LINEST output values ​​are incorrect except for the value in the fifth row and second column (residual sum of squares) if you specified "TRUE" to get statistics.

If you specified the fourth parameter of the function as "TRUE" to get statistics, you need to correct the output for incorrect values. The values ​​on lines 2, 3 and 4 of the extended output are incorrect; the value in column 1 of row 5 is also incorrect. You need to fix them.

Make a copy of the first line of LINEST output elsewhere on the sheet. If you specified "TRUE" for statistics, reserve four blank lines below this copy. Copy line 5 column 2 from original LINEST output to line 5 column 2 of the new output space

Step one: calculate the correct number of degrees of freedom to replace the value in column 2 of row 4 of the LINEST output. Find the number of parameters in the model; this is c + 1. You can use the COUNT function to count the number of columns in the extended X-matrix. Then add all the values ​​in the X-matrix indicator column. Suppose four strings have all zero values. Use the SUM function: this gives r - 4 = the number of rows with "1" in the indicator column. The correct degrees of freedom are the difference: SUM (indicator column) - COUNT (magnified X-matrix columns). This is the value to be placed in the row 4 column of the new output space.

Step two: fix line 2 and line 3 column 2. Split the wrong df (line 4 column 2) in the original LINEST output with the correct df (line 4 column 2) in the new output space. Take the square root of this quotient. Multiple values ​​in row 2 and column 3 in row 3 in the original LINEST output space with this correction factor to get the correct standard errors of the parameters and the correct standard error Y.

Step three: correct the regression sum of squares. The original LINEST output has a value for sum of squares due to non-regression regression for the mean in column 1 of row 5 of the output; we want the regression sum of squares to be adjusted for the mean. We need to calculate the correction for the mean. It is the sum of the squares of the squares of the Y vectors divided by the sum of the values ​​in the indicator column. Subtract that from the value in column 1 of row 5 of the original LINEST output, and place the answer in column 1 of row 5 of the new output space.

Step four: correct the ratio F in column 4 of row 1. We need to compute the mean squares due to regression and due to residuals. The mean square due to regression (numerator in F-ratio) is the value in column 1 of row 5 of the new output space divided by c, the number of columns in the original X-matrix before incrementing. The mean square due to residuals (denominator in F-ratio) is column 2 of row 5 of the new output space, separated by column 2 of row 4 of the new output space. Calculate the ratio F from these two intermediate values ​​and place the result in column 1 of row 4 of the new output space.

Step five: correct the R-squared value in column 1 of row 3. That's 1 - (column of row 5 divided by the sum of column 1 of row 5 and row 5 of column 2) using the values ​​from the new output space.

Test your work: make a copy of the expanded X matrix and Y vector elsewhere in the spreadsheet. Replace all records with zero for those strings that have zero in the indicator variable. Delete all cells in rows with zeros by moving the cells up. You should now have an X-matrix and a Y-vector with fewer rows, but no missing values. Delete the indicator column. Now use LINEST to create a regression model for this reduced dataset, but this time set the third parameter to TRUE (including a constant). These results should be identical to the results you have in the new output space.