Linest Array vertical answer return

  • Thread starter Thread starter Baffeled
  • Start date Start date
B

Baffeled

I am trying to perform a rolling linest function, and as the formula returns
2 answers I need them to be on top of each other, so as I drag the formula
across it gives me both answers. I have selected two cells above each other
but the formula returns the wrong value for the constant C.

So below each colum of data I need to produce a linest function to return
the Mx+C (M and C), then be able to drag this accros each colum to return the
linest function for each colum.
 
Hi,

Have you tried extracting the individual LINEST results from the array?

=INDEX(LINEST(B1:B10,A1:A10,TRUE,TRUE),1,1) will give you the gradient, and
=INDEX(LINEST(B1:B10,A1:A10,TRUE,TRUE),1,2) will give you the intercept.

Alternatively, you could just use the SLOPE and INTERCEPT functions.

Dave
 
Thank you, works perfect!

Dave Curtis said:
Hi,

Have you tried extracting the individual LINEST results from the array?

=INDEX(LINEST(B1:B10,A1:A10,TRUE,TRUE),1,1) will give you the gradient, and
=INDEX(LINEST(B1:B10,A1:A10,TRUE,TRUE),1,2) will give you the intercept.

Alternatively, you could just use the SLOPE and INTERCEPT functions.

Dave
Click to expand...
 
Back
Top