Reverse output of the Linest function
 

Hi

I want to use the Linest function for multiple regression however it has an
annoying feature where by the coeffecients are listed in descending order
along the row.

Is there a way to flip a row (or column for that matter) so that the
constant is shown first followed by the remain terms in ascending order? Also
can any error outputs be forced to the value zero?

I want to avoid macros and using the sort menu so that the end users can
just enter data and observe the output, which in this case is statistics from
mixed modelling.

I found the function pfitdata on the internet
(http://www.xlxtrfun.com/XlXtrFun/XlXtrFun.htm) which does exactly what I
want in terms of output but only fits polynomials and does not do multiple
linear regression.

Regards
Nick Flyger
Senior Biomechanist
Malaysian Institute of Sport

Reverse output of the Linest function
 

=INDEX(C1:F1,{4,3,2,1})
reverses the order of values in C1:F1.

Jerry

"Nick Flyger" wrote:

Hi

I want to use the Linest function for multiple regression however it has an
annoying feature where by the coeffecients are listed in descending order
along the row.

Is there a way to flip a row (or column for that matter) so that the
constant is shown first followed by the remain terms in ascending order? Also
can any error outputs be forced to the value zero?

I want to avoid macros and using the sort menu so that the end users can
just enter data and observe the output, which in this case is statistics from
mixed modelling.

I found the function pfitdata on the internet
(http://www.xlxtrfun.com/XlXtrFun/XlXtrFun.htm) which does exactly what I
want in terms of output but only fits polynomials and does not do multiple
linear regression.

Regards
Nick Flyger
Senior Biomechanist
Malaysian Institute of Sport

Reverse output of the Linest function
 

Thanks, hadn't seen that before and it works great. I devised a longer
workaround using Hlookup, however it meant a slightly different formula in
the first cell.

Is there a simply way to enter a descending array into the index formula.
For example in matlab you would write order=[10:-1:1] to get an array called
order descending from 10 to 1 in increments of 1?

Cheers
Nick

"Jerry W. Lewis" wrote:

=INDEX(C1:F1,{4,3,2,1})
reverses the order of values in C1:F1.

Jerry

"Nick Flyger" wrote:

Hi

I want to use the Linest function for multiple regression however it has an
annoying feature where by the coeffecients are listed in descending order
along the row.

Is there a way to flip a row (or column for that matter) so that the
constant is shown first followed by the remain terms in ascending order? Also
can any error outputs be forced to the value zero?

I want to avoid macros and using the sort menu so that the end users can
just enter data and observe the output, which in this case is statistics from
mixed modelling.

I found the function pfitdata on the internet
(http://www.xlxtrfun.com/XlXtrFun/XlXtrFun.htm) which does exactly what I
want in terms of output but only fits polynomials and does not do multiple
linear regression.

Regards
Nick Flyger
Senior Biomechanist
Malaysian Institute of Sport