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

Ping Chip Pearson
 

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" 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

Ping Chip Pearson
 

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

Ping Chip Pearson
 

No need to array enter.

Biff

"Ashish Mathur" wrote in message
...
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

Ping Chip Pearson
 

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" 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

Ping Chip Pearson
 

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

Ping Chip Pearson
 

Thanks to all and Shatin really cleared it up completely.

Thanks again,
lomax
"Shatin" wrote in message
...
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" 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