Using INDIRECT in INDEX(LINEST.. ) function

=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.


Incoherent wrote:
> =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

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 V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Incoherent" <[email protected]> wrote in message
news:[email protected]...
> =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
>

Sorry but that is not correct
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Shaz" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
> Incoherent wrote:
>> =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
>

Hmmmm

if I use double quoations it works for me

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

=3D-2.84957242987079E-15


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


Bernard Liengme wrote:
> 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 =3DINDEX(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 R=
EF
> 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' wit=
h?
> ------------
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Incoherent" <[email protected]> wrote in message
> news:[email protected]...
> > =3DINDEX(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.
> >
> > =3DINDEX(LINEST(INDIRECT(S14),$F$16:$N$16^{1;2;3}),1) works fine.
> >
> > =3DINDEX(LINEST(INDIRECT(S14),INDIRECT($S$15)^{1;2;3}),1) when
> > S15=3D"$F$16:$N$16"
> > and
> > =3DINDEX(LINEST(INDIRECT(S14),INDIRECT($S$15)),1) when
> > S15=3D"$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
> >