Using INDIRECT in INDEX(LINEST.. ) function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

=INDEX(LINEST(F17:N17,F16:N16^{1;2;3}),1)

The above formula is part of a much longer formula.

I want to repace the references with the INDIRECT function but I am getting
#REF when I put INDIRECT in as the second argument.

=INDEX(LINEST(INDIRECT(S14),$F$16:$N$16^{1;2;3}),1) works fine.

=INDEX(LINEST(INDIRECT(S14),INDIRECT($S$15)^{1;2;3}),1) when
S15="$F$16:$N$16"
and
=INDEX(LINEST(INDIRECT(S14),INDIRECT($S$15)),1) when S15="$F$16:$N$16^{1;2;3}"
both give #REF

How can I make this work? The Reference size changes depending on input data
set size so I figure I must use INDIRECT. It obviously does not like the
^{1;2;3} array formula part.

Thanks
 
The indirect function should be like this:

=indirect("A1"), you are not puting the double quotations inside.
Therefore your formula should be:

=INDEX(LINEST(INDIRECT("S14"),INDIRECT("$S$15")^{1;2;3}),1)

cheers.
 
I put some x-values in F1:J1 and y-values in F2:J2
with D1 having text entry F2:J2 and D2 having F1:J1
I used this =INDEX(LINEST(INDIRECT(D1),INDIRECT(D2)^{1;2;3}),1) and it
worked.

Changed D2 to $F$1:$J$1 and it still worked but, not unexectedly, I got REF
with $F$1:$J$1^{1,2,3} because this will evaluate to
INDIRECT('$F$1:$J$1^{1,2,3}) which is not a range reference
So your last formula is not possible but the one before that is.
Want to send me the file (private email, not the newsgroup) to 'play' with?
 
Hmmmm

if I use double quoations it works for me

=INDEX(LINEST(INDIRECT("C1:C4"),INDIRECT("D1:D4")^{1,2,3}),1)
C D
1 1
5 5
3 3
4 4

=-2.84957242987079E-15


Only you need to use commas instead of semicolon´s. in the array. I
think both x and y ranges must be same length.
 
Back
Top