Ping Chip Pearson

L

Lomax

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
 
C

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
 
A

Ashish Mathur

Hi,

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

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

Regards,

Ashish Mathur
 
B

Biff

No need to array enter.

Biff

Ashish Mathur said:
Hi,

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

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

Regards,

Ashish Mathur
Click to expand...
 
S

Shatin

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.
 
A

Aladin Akyurek

Another option is:

H4: 5

H5:

=LARGE(F4:F16,H4)

H6:

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