Average Top N performance in table

  • Thread starter Thread starter Adam
  • Start date Start date
A

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
 
=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.
 
Back
Top