Mean of Highest 20% of list of numbers when list of numbers may vary?

Hello,

I'm an trying to create a formula which will calculate the mean of the highest 20% of a list of numbers. The problem I have is that depending upon earlier formula's, the number of numbers which need to be taking into account within the list vary.

For example:-

If I have a list such as:-

Cell Number Value In Cell
1 1
2 2
3 3
4 N/A
5 5
6 0
7 0
8 8
9 9
10 10

The "N/A" values must not be included and the "0" Values must also not be included. So I need to identify the highest 20% of the cells with only the valid values (i.e. excluding N/A and 0) which need to be automatically discounted.

I'm finding it hard to get my head round it, so what I have written above is probably as clear as mud.

Any help would be appreciated.

Many thanks,

Andrew

Try


=AVERAGE(IF(B1:B1000>0,IF(B1:B1000>=INT(LARGE(B1:B1000,COUNT(B1:B1000)*0.2)),B1:B1000)))

Entry with Ctrl+Shift+Enter

Hi

You can try the array formula

Formula:

Please Login or Register  to view this content.

Regards

As per the data out of 7 numbers 1.4 are to be taken for mean. In such case what number is to be taken 1 or 2.

Modified the formula ,,


=AVERAGE(IF(($A$1:$A$1000>0)*($A$1:$A$1000>=LARGE($A$1:$A$1000,INT(COUNT($A$1:$A$1000)*0.2))),$A$1:$A$1000))

Thanks all,

I will give the formula a try and let you know how I get on.

Regards,

Andrew

Thank you: would appreciate any feedback.