rank or percentage

[hallhv01]

If you have any number of rows and 2 columns, is there a way to tell excel to calculate a formula for only a top percentage of the columns? Say you want the average of column A for the top 20% of the rows. I want to be able to vary the percentage by typing a percent in a cell and using it in the formula if possible.
Thanks

[Mark W.]

{=AVERAGE(LARGE(A2:A10,ROW(INDIRECT("1:"&ROUND(COUNT(A2:A10)*C2,0)))))}

where cell C2 contains your percentage.

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.


[ This Message was edited by: Mark W. on 2002-05-21 07:03 ]

[Aladin Akyurek]

On 2002-05-21 06:57, hallhv01 wrote:
If you have any number of rows and 2 columns, is there a way to tell excel to calculate a formula for only a top percentage of the columns? Say you want the average of column A for the top 20% of the rows. I want to be able to vary the percentage by typing a percent in a cell and using it in the formula if possible.
Thanks

Lets say that the data in A starts in row 2.

In C1 enter:

=MATCH(9.99999999999999E+307,A:A)

In C2 enter: 10% [ your condition ]

In C3 enter:

=AVERAGE(OFFSET($A$2,0,0,ROUND(C2*C1,0),1))

Aladin

[Mark W.]

Obviously, Aladin and I have a different interpetation of "top percentage". Did you mean "first n% in the list" or "top n% of the values"?

[hallhv01]

I have the data sorted by date, decending order, so it could be the top N% of the data, but it could also be the top n% of the values, I suppose since I have it sorted it would come out the same. (I need the top N% of the dates)