complete missing values within a sequence

[patterson1966]

Hi all, I have blanks in a sequence..

2.2
blank
blank
blank
8.43
10.98

How can I fill in the blanks to complete a growth curve?
Many thanks

 

[hrlngrv]

If this partial sequence were in B2:B7, so numbers in B2, B6 and B7, blanks in B3:B5, one way to flesh it out would be

B3: =GROWTH({1;0;0}*B$2+{0;1;0}*B$6+{0;0;1}*B$7,{1;5;6},ROWS(B$2:B3))

Fill B3 down into B4:B5.

 

[Asala42]

Chart the 3 points you have in a scatter plot(1,5,6 vs 2.2, 8.63, 10.98), then add a trendline and see if you can find a perfect fit. Say a 2nd degree polynomial for example

You can show the formula for the polynomial on the chart, and then calculate for the points when x=2, 3, and 4.

 

[patterson1966]

Thanks very much hrlngrv, that seems to work perfectly thanks. Just noticed I have 4 blanks, not 3, though. Would that be a different formula?

Thanks again

 

[hrlngrv]

You may need to generalize my previous formula. If the entire range were, say, B3:B12, and only cells B3, B6, B11 and B12 had values, you could use

B4:
=GROWTH(B$3*{1;0;0;0}+B$6*{0;1;0;0}+B$11*{0;0;1;0}+B$12*{0;0;0;1},{3;6;11;12},ROW())

copy B4 and paste into B5 and B7:B10. The point is you're making an array out of the cells initially containing values by multiplying each separately by a single-column array of zeros and one 1 corresponding to where the cell value should be in the array. The second argument to GROWTH should be an array of the row indices, and the third argument should be the row index for the interpolated value.