Ping Chip Pearson

I have a list of $ values in F4-F16 need to average the largest 5 amounts.

I copied this from Your website and made a slight mod to accommodate the 5
instead of 10 as on the site.
={AVERAGE(LARGE(F4:F16,ROW(INDIRECT("1:5"))))}

I understand all but the (indirect("1:5)). I hate to be a bother but could
you explain this part? What does the indirect function perform?

Thanks lomax

The INDIRECT("1:5") converts the text string "1:5" to a range
reference that can be used by the ROW function.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Lomax" <[email protected]> wrote in message
news:MLh4f.88$282.15@dukeread08...
>I have a list of $ values in F4-F16 need to average the largest
>5 amounts.
>
> I copied this from Your website and made a slight mod to
> accommodate the 5 instead of 10 as on the site.
> ={AVERAGE(LARGE(F4:F16,ROW(INDIRECT("1:5"))))}
>
> I understand all but the (indirect("1:5)). I hate to be a
> bother but could you explain this part? What does the indirect
> function perform?
>
> Thanks lomax
>

Hi,

Another way to do it is as follows (Ctrl+Shift+Enter):

=AVERAGE(LARGE(F4:F16,{1,2,3,4,5}))

Regards,

Ashish Mathur

"Lomax" wrote:

> I have a list of $ values in F4-F16 need to average the largest 5 amounts.
>
> I copied this from Your website and made a slight mod to accommodate the 5
> instead of 10 as on the site.
> ={AVERAGE(LARGE(F4:F16,ROW(INDIRECT("1:5"))))}
>
> I understand all but the (indirect("1:5)). I hate to be a bother but could
> you explain this part? What does the indirect function perform?
>
> Thanks lomax
>
>
>

No need to array enter.

Biff

"Ashish Mathur" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Another way to do it is as follows (Ctrl+Shift+Enter):
>
> =AVERAGE(LARGE(F4:F16,{1,2,3,4,5}))
>
> Regards,
>
> Ashish Mathur
>
> "Lomax" wrote:
>
>> I have a list of $ values in F4-F16 need to average the largest 5
>> amounts.
>>
>> I copied this from Your website and made a slight mod to accommodate the
>> 5
>> instead of 10 as on the site.
>> ={AVERAGE(LARGE(F4:F16,ROW(INDIRECT("1:5"))))}
>>
>> I understand all but the (indirect("1:5)). I hate to be a bother but
>> could
>> you explain this part? What does the indirect function perform?
>>
>> Thanks lomax
>>
>>
>>

I was also completely confused by the formula ROW(INDIRECT("1:5")) often
used in array formulas until I read John Walkenback's Excel Formulas 2003.

In dealing with arrays, we often want to generate an integer array like this
{1,2,3,4,5}. The row() function can achieve this.

So why not simply row(1:5)? The reason is if rows have been added to or
deleted from the worksheet, row(1:5) may become row(2:6), row(3:7)... In
fact all sorts of things can happen. The INDIRECT function is used to make
sure that this will not happen. Whether you add or delete rows,
ROW(INDIRECT("1:5")) will always return the {1,2,3,4,5} array.

"Lomax" <[email protected]> wrote in message
news:MLh4f.88$282.15@dukeread08...
> I have a list of $ values in F4-F16 need to average the largest 5 amounts.
>
> I copied this from Your website and made a slight mod to accommodate the 5
> instead of 10 as on the site.
> ={AVERAGE(LARGE(F4:F16,ROW(INDIRECT("1:5"))))}
>
> I understand all but the (indirect("1:5)). I hate to be a bother but
could
> you explain this part? What does the indirect function perform?
>
> Thanks lomax
>
>

Another option is:

H4: 5

H5:

=LARGE(F4:F16,H4)

H6:

=(SUMIF(F4:F16,">"&H5)+(H4-COUNTIF(F4:F16,">"&H5))*H5)/H4

Lomax wrote:
> I have a list of $ values in F4-F16 need to average the largest 5 amounts.
>
> I copied this from Your website and made a slight mod to accommodate the 5
> instead of 10 as on the site.
> ={AVERAGE(LARGE(F4:F16,ROW(INDIRECT("1:5"))))}
>
> I understand all but the (indirect("1:5)). I hate to be a bother but could
> you explain this part? What does the indirect function perform?
>
> Thanks lomax
>
>

Thanks to all and Shatin really cleared it up completely.

Thanks again,
lomax
"Shatin" <[email protected]> wrote in message
news:[email protected]...
>I was also completely confused by the formula ROW(INDIRECT("1:5")) often
> used in array formulas until I read John Walkenback's Excel Formulas 2003.
>
> In dealing with arrays, we often want to generate an integer array like
> this
> {1,2,3,4,5}. The row() function can achieve this.
>
> So why not simply row(1:5)? The reason is if rows have been added to or
> deleted from the worksheet, row(1:5) may become row(2:6), row(3:7)... In
> fact all sorts of things can happen. The INDIRECT function is used to make
> sure that this will not happen. Whether you add or delete rows,
> ROW(INDIRECT("1:5")) will always return the {1,2,3,4,5} array.
>
> "Lomax" <[email protected]> wrote in message
> news:MLh4f.88$282.15@dukeread08...
>> I have a list of $ values in F4-F16 need to average the largest 5
>> amounts.
>>
>> I copied this from Your website and made a slight mod to accommodate the
>> 5
>> instead of 10 as on the site.
>> ={AVERAGE(LARGE(F4:F16,ROW(INDIRECT("1:5"))))}
>>
>> I understand all but the (indirect("1:5)). I hate to be a bother but
> could
>> you explain this part? What does the indirect function perform?
>>
>> Thanks lomax
>>
>>
>
>