Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with this reference problem
Thanks a lot ! Tried your first suggestion and it works, but the
alternative doesn't though. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A reference problem - I THINK? | Excel Discussion (Misc queries) | |||
Problem w/ cell reference | Excel Discussion (Misc queries) | |||
Reference problem | Excel Discussion (Misc queries) | |||
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions | |||
Another Reference Problem | Excel Programming |