Using INDIRECT in INDEX(LINEST.. ) function

[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

 

[Shaz]

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.

 

[Bernard Liengme]

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?

 

[Bernard Liengme]

Sorry but that is not correct

 

[Shaz]

Hmmmm

if I use double quoations it works for me

=INDEX(LINEST(INDIRECT("C1:C4"),INDIRECT("D14")^{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.