trendline for arrays with missing data points

**melnik** · 02-08-2011, 04:25 AM

Dear experts,

I have a dataset which is an array of measurements with one data point missing.
When I plot a logarithmic trendline, I get a certain R-squared for the fit. Because I cannot leave this data point empty, I wonder - how exactly excel deals with this empty data point when calculating the fit? I've been trying to model it using the coefficients of the log fit (using the LINEST function) and still did not get the exact same R-squared.
I've already searched all the forums on the net and tried to "fill" the missing data by the auto fill function and using the TREND function on the array of data preceding the missing data point.
Below is an example of the data (8th data point is missing):

8.659048
31.61216
41.75575
47.64559
45.23398
52.12204
41.56181

50.9005
57.66537

The R-squared for the log fit is 0.84.

Thank you very much in advance!

Anna

**shg** · 02-08-2011, 09:45 AM

If you get rid of the blank, you can use LINEST:

Please Login or Register  to view this content.

**melnik** · 02-09-2011, 06:33 AM

Dear shg,

Thank you for your response.

I must have been unclear with my question:

What I'm looking for is how Excel fills in the missing data point.

I can't get rid of the blanks, since I have to maintain the full length of the array.

Moreover, for the sake of the discussion, when I get rid of the blank data and do the fit again (for the array of 9 elements instead of 10) I get the R-squared of 0.8476, which is pretty close but still different from 0.84 that I get when fitting the full array of 10 elements, with the blank included. So what Excel is doing there? What do I have to put in that blank cell in order to get the exact same R-squared? What is the formula to fill the blank in order to get the exact same R-squared but still keep the length of the array?

Thanks!

Anna

**shg** · 02-09-2011, 01:22 PM

The line chart ignores the missing value, and uses the categorical series 1,2,3,...9, which is what I did with LINEST by closing up the blank value, which gives the same R2 value as you see on your chart.

LINEST doesn't forgive blanks.

Let's retreat and explain your objective.

**melnik** · 02-10-2011, 01:09 PM

Thanks shg,

it turns out that the solution is instead of LN({1;2;3;4;5;6;7;9;10}) as you suggested
to use LN({1;2;3;4;5;6;7;8;9}) as the input for the LINEST.

Only this way I'm getting rid of the blanks and have the exact same a,b and R square as in the chart with a blank cell.

Anna