Formula to calculate missing data points

[hstaubyn]

Hi,

I have a table of data, but it has gaps in it. I know all the 'x' values (they go from 1 to 69 linearly), but some of the 'y' values are missing.

I know the 'y' values vary cyclically so would have thought that excel would have a formula that looks at the whol table, effectively draws a line graph in its head and then fills in the missing data points with values based on said line graph.

e.g.

x: 1----2----3----4----5----6----7----8----9----10
y: 23--18---21------28---37---25---17-------23

I want to put formulae in the cells corresponding to x values of 4 and 9 and have the formula calculate the value based on the other values on the same row.

I have tried placing the following in cells P9, U9 & AG9 (does not correspon to columns above):

=TREND((O9,Q9:T9,V9:AF9,AH9:AR9),O7:AR7)

this does not work! Trend cannot read multiple ranges for "known 'y's". Does anyone know of a formula that does? Or failing that, a UDF somewhere that does this?

Thanks,

H

 

[shg]

      A- B- C- -D-- E- F- G- H- I- J-
  1    1  2  3    4  5  6  7  8  9 10
  2   23 18 21 24.5 28 37 25 17 20 23

The formula in D2 could be

=TREND(CHOOSE({1,2}, C2,E2), CHOOSE({1,2}, C1,E1), D1)

or

=(D1-C1)/(E1-C1) * (E2-C2) + C2

 

[hstaubyn]

Ok thanks for that, I'm going to have to get my head round the first one...

I guess you're going for a kind of moving average formula which is great, except that it doesn't take account of multiple adjacent empty cells. (I know I didn't mention this in my post.)

Trend line when applied to a graph does work even with adjacent empty values. Now if this is so why isn't there a formula to return 'y' values from a trend line derived approach?

Seems daft to me that you can produce a visual line (trend line on a graph) which has to have numerical data behind it, but you can't access that numerical data.

Or am I missing something???

 

[shg]

You can return the coefficients of a trendline using LINEST.

However, there's a difference between a linear interpolation of adjacent points and a (linear) trendline, which is a best fit of a straight line to a set of points. Which are you trying to do?

 

[hstaubyn]

Shg,

thanks for your reply. I am happy with the LINEST function, but as you say, it only approximates a straight line. I am after a formula that gives the powers, coefficients etc. used by the trendline on a graph.

Surely it must be possible to have a formula that gives these values and for all the different types of trendline (Linear, Logarithmic, Polynomial - with choice of order, Power, Moving Average - with choice of period). It would be possible to create these formulae from scratch, but I was wondering if

a. Microsoft had thought of this and included the necessaries
b. If not, then someone knew where to find a UDF that does this

Cheers,

H

 

[shg]

LINEST will give you all those results except moving average, which is not a regression, but the x and y arrays must be chock full of numbers, no blanks, text, ...

Linear: =LINEST(y, x)

Log: =LINEST(y, LN(x))

Polynomial: =LINEST(y, x^{1,2}), =LINEST(y, x^{1,2,3}), ...

Power: =LINEST(LN, LN(x))

See also http://office.microsoft.com/en-us/excel-help/perform-a-regression-analysis-HA001111963.aspx?redir=0