When I use the INDIRECT command, as in the following example,

PERCENTILE(INDIRECT("$FM"&$Y140):$FM140, FN$1)

it works fine until I insert a new column anywhere before the column
containing the IDIRECT command. If I do that, the first parameter in the
command changes,

PERCENTILE(INDIRECT("$FL"&$Y140):$FM140, FN$1)

notwithstanding the $ that I had put there to prevent its changing. Is
there any way to avoid that?
--
Delaware Mac

Try putting the FM into a cell also to be used as part of your indirect.
Let's say you put ="FM" into Y139, then your formula would be
=PERCENTILE(INDIRECT(Y139 & Y140):$FM140,FN$1)


"Delaware Mac" wrote:

When I use the INDIRECT command, as in the following example,

PERCENTILE(INDIRECT("$FM"&$Y140):$FM140, FN$1)

it works fine until I insert a new column anywhere before the column
containing the IDIRECT command. If I do that, the first parameter in the
command changes,

PERCENTILE(INDIRECT("$FL"&$Y140):$FM140, FN$1)

notwithstanding the $ that I had put there to prevent its changing. Is
there any way to avoid that?
--
Delaware Mac

Dear Jlatham,

Thanks for your suggestion, but I see now that I botched the explanation,
and therefore misled you, when I originally stated the problem. I should have
had these two lines instead:

(B4: This ln is in col FN): PERCENTILE(INDIRECT("FM"&$Y140):FM140, FN$1)

(Aft: It's now in col FO): PERCENTILE(INDIRECT("FM"&$Y140):FN140, FO$1)

What happens is not that the first parameter, "FM", changes, but that it
doesn't automatically get updated to "FN" like the other parameters when I
add a new column anywhere between columns Y and FM. So I actually want "FM"
to automatically change, but it doesn't.

What I want to do is get a running percentile of a range of numbers in a
given column (originally FM) using column Y to hold the beginning row number
(which stays the same for a while, then increases, based on a formula). I
occasionally need to add new columns, and wanted to avoid having to update
the INDIRECT commands manually, although manual updates are not that much of
a hassle if that's the only solution.

--
Delaware Mac


"Delaware Mac" wrote:

When I use the INDIRECT command, as in the following example,

PERCENTILE(INDIRECT("$FM"&$Y140):$FM140, FN$1)

it works fine until I insert a new column anywhere before the column
containing the IDIRECT command. If I do that, the first parameter in the
command changes,

PERCENTILE(INDIRECT("$FL"&$Y140):$FM140, FN$1)

notwithstanding the $ that I had put there to prevent its changing. Is
there any way to avoid that?
--
Delaware Mac