Quadratic regression using linest in Excel - incomplete data set

Question

I have previously used LINEST for linear regression on incomplete data sets. The thread Missing values in MS Excel LINEST, TREND, LOGEST and GROWTH functions gave me all the answers I needed.

My data set was:

D    E
12   1,23
12,5 1,28
13   1,33
13,5 missing    
14   1,41
14,5 1,47
15   1,53
15,5 1,56
16   missing
16,5 1,66
17   1,70
17,5 1,72
18   1,66

Using the trick mentioned in the link I created a new table with an extra column

A           B    C
1           12   1,23
1           12,5 1,28
1           13   1,33
0           13,5 0  
1           14   1,41
1           14,5 1,47
1           15   1,53
1           15,5 1,56
0           16   0
1           16,5 1,66
1           17   1,70
1           17,5 1,72
1           18   1,66

Then using the formular LINEST(C1:C13;A1:B13;1;0) A and B of y=Ax+B was calculated.

Now my problem is similar, but this time I need to do quadratic regression. Normaly using a complete data set I would use the formular LINEST(E1:E13;D1:D13^{1\2};1;0) creating an array of A, B and Y of y=Ax^2+Bx+C

The problem is LINEST does not seem to allow me to use the same solution as for linear regression in which case I would have used LINEST(C1:C13;A1:B13^{1\2};1;0), but it doesn't work :-(

Can anyone help?

Answer 1

The first step is to create a compact listing of the data without missing:

In F1 enter 1.
In F2 enter:

=IF(E2<>"missing",1+MAX($F$1:F1),"")

and copy down:

In G1 and H1 enter:

=IFERROR(INDEX(D:D,MATCH(ROW(),$F:$F,0)),"")
=IFERROR(INDEX(E:E,MATCH(ROW(),$F:$F,0)),"")

and copy these down:

To get the coefficients for:

Y = A * X² + B * X + C
(where the X-values are in column G and the Y-values are in column H)

For A use:

=INDEX(LINEST(H1:H11,G1:G11^{1,2}),1)

For B use:

=INDEX(LINEST(H1:H11,G1:G11^{1,2}),1,2)

For C use:

=INDEX(LINEST(H1:H11,G1:G11^{1,2}),1,3)