Need help with this reference problem
 

For this array formula,

=AVERAGE(SMALL(A1:A60,ROW(INDIRECT("1:10"))))

I'd like to modify it such that it will look up another cell (e.g. C5) for
the number of cells I want to average instead of a constant of 10.

I tried to change it to
=AVERAGE(SMALL(A1:A60,ROW(INDIRECT("1:N")))) and name the C5 cell "N" and
apparently it didn't work.

How should I fix it? Thx in advance.

Need help with this reference problem
 

without testing, try
ROW(INDIRECT("1:"&c5&"))))

--
Don Guillett
SalesAid Software

"<<<Golf4U" wrote in message
news:Pb7Uc.33543$fz2.3543@edtnps89...
For this array formula,

=AVERAGE(SMALL(A1:A60,ROW(INDIRECT("1:10"))))

I'd like to modify it such that it will look up another cell (e.g. C5) for
the number of cells I want to average instead of a constant of 10.

I tried to change it to
=AVERAGE(SMALL(A1:A60,ROW(INDIRECT("1:N")))) and name the C5 cell "N" and
apparently it didn't work.

How should I fix it? Thx in advance.

Need help with this reference problem
 

Hi
try:
=AVERAGE(SMALL(A1:A60,ROW(INDIRECT("1:" & C5))))

or as alternative:
=AVERAGE(IF(A1:A60<=SMALL(A1:A60,C5)))

--
Regards
Frank Kabel
Frankfurt, Germany

"<<<Golf4U" schrieb im Newsbeitrag
news:Pb7Uc.33543$fz2.3543@edtnps89...
For this array formula,

=AVERAGE(SMALL(A1:A60,ROW(INDIRECT("1:10"))))

I'd like to modify it such that it will look up another cell (e.g.
C5) for
the number of cells I want to average instead of a constant of 10.

I tried to change it to
=AVERAGE(SMALL(A1:A60,ROW(INDIRECT("1:N")))) and name the C5 cell "N"
and
apparently it didn't work.

How should I fix it? Thx in advance.

Need help with this reference problem
 

Hi Don
I think you left a &" :-).

probably should read:
ROW(INDIRECT("1:"&c5))))

--
Regards
Frank Kabel
Frankfurt, Germany

"Don Guillett" schrieb im Newsbeitrag
...
without testing, try
ROW(INDIRECT("1:"&c5&"))))

--
Don Guillett
SalesAid Software

"<<<Golf4U" wrote in message
news:Pb7Uc.33543$fz2.3543@edtnps89...
For this array formula,

=AVERAGE(SMALL(A1:A60,ROW(INDIRECT("1:10"))))

I'd like to modify it such that it will look up another cell (e.g.
C5) for
the number of cells I want to average instead of a constant of 10.

I tried to change it to
=AVERAGE(SMALL(A1:A60,ROW(INDIRECT("1:N")))) and name the C5 cell
"N" and
apparently it didn't work.

How should I fix it? Thx in advance.

Need help with this reference problem
 

Thanks a lot ! Tried your first suggestion and it works, but the
alternative doesn't though.