Linest fitting data on multiple worksheets

[Tom D]

I am trying to get linest to fit a line to data on multiple work sheets.
It won't do it. Here's what I am trying to do :

=Linest({sheet1!B1:B10,sheet2!B1:B10},{sheet1!A1:A10,sheet2!A1:A10},,TRUE)

I highlight a 2x2 set of cells, type this in, and it says I have an
error in my formula. It gives me the same problem when I try to use the
cutesy gui for creating the formula.

Is there a work around, or do I just need to combine all the data into
one worksheet? Not the ideal solution.

Thanks!

Tom D

 

[Harlan Grove]

I am trying to get linest to fit a line to data on multiple work sheets.
It won't do it. Here's what I am trying to do :

=Linest({sheet1!B1:B10,sheet2!B1:B10},{sheet1!A1:A10,sheet2!A1:A10},,TRUE)

I highlight a 2x2 set of cells, type this in, and it says I have an
error in my formula. It gives me the same problem when I try to use the
cutesy gui for creating the formula.

Is there a work around, or do I just need to combine all the data into
one worksheet? Not the ideal solution.

You don't need to combine the data into a single workbook, but you do need to
create single arrays from multiple ranges. First, array constants containing
range references aren't supported - array constants may *only* contain constant
numbers, text and error values.

It looks like you want these stacked on top of each other, so both X and Y
variables would be 20-row by 1-column. If so, you'd be best off downloading and
installing Laurent Longre's MOREFUNC.XLL add-in, freely available from

http://longre.free.fr/english/

It contains a function called THREED which would do exactly what you want, so

=Linest(THREED(sheet1!B1:B10,sheet2!B1:B10),
THREED(sheet1!A1:A10,sheet2!A1:A10),,TRUE)