Average Top N performance in table

[Adam]

Hi all

I can only do this within Pivot tables but would like not to put them
within this worksheet due to file size and simplicity etc.

I would like to determine the Top N values in column A, which is a
data table. I'd like to try and get a Top N average.

Are there any Excel functions that do this? There is nothing in Excel
help.

Many thanks
Adam

 

[Bob Phillips]

=AVERAGE(IF(A1:A100>=LARGE(A1:A100,N),A1:A100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

 

[herochenna]

Visit www.ExcelGoodies.Com