| 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
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. |
|
#4
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. |
|
#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 | |||
![Don Guillett[_4_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
