Regresssion analysis, data with gaps

[gruntguru]

I have two dimensional data in two columns. The column of "x" data i
complete (no blank cells). The column of "y" data is incomplete (som
blank cells). I want to produce a third column containing a complet
set of "y" values using a second order polynomial obtained b
regression analysis of the first two columns. Everything I have trie
("TREND" function etc treats the blank cells in the incomplete colum
of "y" data as Zeros. I need something that will ignore the blanks.
have been able to get the answers I need by "x y scatter" graphing th
first two columns (excel ignores the blanks), then fitting a 2nd orde
polynomial trendline and displaying the trendline formula on the graph
I then have to type the formula constants back into the spreadsheet t
produce my third column. This is a tedious process as I need t
frequently update the values in my second column and re-calculate th
third column

 

[duane]

the way i would approach it would be to write a macro that copied the
and y data to a new sheet, deleted the rows without y data, and the
perform the regression, and then use those results to create th
calculated y values on the original sheet

 

[gruntguru]

Thanks Duane - I will try that. Of course I will need to run the macr
every time I adjust the raw data but that will still be a lot quicke
than what I am doing now

 

[duane]

great - to get a non lnear fit you of course need to take the ln(x) an
ln(y) and then get your slope and intercept on them, and then to ge
your calculated y = something like exp(ln(x)*slope + intercept)

I'll take a shot at the macro if you'd like.

Joh

 

[Harlan Grove]

I have two dimensional data in two columns. The column of "x" data is
complete (no blank cells). The column of "y" data is incomplete (some
blank cells). I want to produce a third column containing a complete
set of "y" values using a second order polynomial obtained by
regression analysis of the first two columns. Everything I have tried
("TREND" function etc treats the blank cells in the incomplete column
of "y" data as Zeros. . . .

You should read the entire thread beginning with

http://groups.google.com/[email protected]

If you're using Excel 2003 it gives some work-arounds. You should also check
if the latest service pack addresses this.

 

[Bernard Liengme]

Using the data from Tushar
x y
2
4 0.0214
8 0.0232
12 0.0643
16 0.0778
20 0.1025

I can confirm that Excel2003 with the latest patch (See Knowledge Base
834691) now gives the correct slope (0.00542) and intercept (-0.0072) with
missing data in first row.