Prev: MGRS to Lat/Long
Next: Hyperlinks in Excel
From: Nick Flyger Nick on 1 Oct 2006 21:58
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
From: Jerry W. Lewis on 2 Oct 2006 00:30
=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
From: Nick Flyger on 2 Oct 2006 03:02
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
 | 
Pages: 1
Prev: MGRS to Lat/Long
Next: Hyperlinks in Excel