LINEST on non-contiguous data

[sphenisc]

Hi,

Subject line says it all,really...

I've tried

=LINEST({e2,e4},{D2,D4})

No luck - any ideas

Thanks

 

[Jerry W. Lewis]

See help for "About array formulas and array constants" subtopic "Using
array constants". The {} syntax is limited to constants, so you could
do =LINEST({3,4},{1,2}), but not what you attempted.

=SLOPE(E2:E4,D24) and =INTERCEPT(E2:E4,D24) will ignore non-numeric
values in row 3. If there are numbers there, you could adapt to ignore
row 3, as in =SLOPE(IF(ROW(E2:E4)=3,,E2:E4),D24) etc.

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

gives ways to calculate the other statistics returned by LINEST when
fitting a straight line.

Jerry
Excel MVP

 

[Bernard Liengme]

I think I would 'cheat' but using a second sheet with formulas such as
=E2,=E4 etc, and use these references in LINEST
I assume the problem is bigger than show since your formula is equivalent to
(E4-E2)/(D4-D2) i.e. slope from two point

Best wishes

 

[Harlan Grove]

...
...

=LINEST({e2,e4},{D2,D4})

No luck - any ideas

=LINEST(E2*{1;0}+E4*{0;1},D2*{1;0}+D4*{0;1})

or

=LINEST(N(OFFSET(E2:E4,{0;2},0,1,1)),N(OFFSET(D24,{0;2},0,1,1)))