LINEST on non-contiguous data

  • Thread starter Thread starter sphenisc
  • Start date Start date
S

sphenisc

Hi,

Subject line says it all,really...

I've tried

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

No luck - any ideas

Thanks
 
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,D2:D4) and =INTERCEPT(E2:E4,D2:D4) 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),D2:D4) 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
 
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
 
...
...
=LINEST({e2,e4},{D2,D4})

No luck - any ideas
Click to expand...

=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(D2:D4,{0;2},0,1,1)))
 
Back
Top