Linest Array vertical answer return

[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.

 

[Dave Curtis]

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

 

[Baffeled]

Thank you, works perfect!

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