LINEST - selection of nonadjacent cell data

  • Thread starter Thread starter thijze
  • Start date Start date
T

thijze

I would like to use the LINEST function on data that is not located in
one single row or column. The data that have to be correlated are
scattered along the worksheet and I would like to select them
manually. How can I do this. If I try
=LINEST((E72;F72;G72);(E75;F75;G75),1,1) or =LINEST({E72;F72;G72};
{E75;F75;G75},1,1) an error occurs. How can I input an array of
nonadjacent cells in this function.
thx
 
Why not use some 'helper cells' with formulas like =E72 to get the numbers
into a contiguous range?
best wishes
 
Lori said:
maybe this:

=LINEST(CHOOSE({1,2,3},E72,F72,G72),CHOOSE({1,2,3},E75,F75,G75),1,1)
Click to expand...

If this gives the OP the desired result, so would

=LINEST(E72:G27,E75:G75,1,1)

Maybe the OP's example formulas were overly simplistic. If a more
realistic example were

=LINEST((E5,G6,I7,K8),(D9,F10,H11,J12),1,1)

then your CHOOSE approach could work for up to 29 observations. A more
general alternative would be something like

=LINEST(E5*(COL4=1)+G6*(COL4=2)+I7*(COL4=3)+K8*(COL4=4),
D9*(COL4=1)+F10*(COL4=2)+H11*(COL4=3)+J12*(COL4=4),1,1)

where COL4 is a named expression defined as =COLUMN($A$1:$D$1). In
short, if you need to use multiple area ranges, you need to convert
them into arrays, and Excel doesn't make that easy or brief.
 
Back
Top