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 