Linest - different columns for x range

[Guest]

Hi,

I hsve to do heaps of regressions and I have 4 x variables. The x variables
are side by side in the column though so I cant do a function like this
Linest (A1:100,B1:E:100) as w variables are in columns b,c,d the the other is
in column j.

Hence I need to do a calc such as linest(a1:a100,b1:d:100,j1:j100). This
doesnt work though as it doesnt acknowldge the varaibles in column J and
produces an error.

Whats the best way to get around this. I am not in a position to reformat
the spreadsheet and have all the x variables side by side in columns
unfortunately.

Thanks

SD

 

[Bernard Liengme]

Insert another worksheet
Use formulas such as =Sheet1!A1 to copy the data from the original sheet
into the new; but make the layout of the new sheet be such that all the x
variables are in consecutive columns.
Use LINEST on this data.
best wishes

 

[Guest]

one method is to insert a worksheet and copy columns A-D to the new sheet
columns A-D and columns J to new sheet column E now do your lineest on the
new sheet
once you have your answer put it on the first sheet and delete the second
sheet