Linest Array vertical answer return
 

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.

Linest Array vertical answer return
 

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" wrote:

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.

Linest Array vertical answer return
 

Thank you, works perfect!

"Dave Curtis" wrote:

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" wrote:

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.