Ranking changing numbers

M

Math Doctor

Thanks for the assistance earlier.

I am working on figures within a chart, and I want to determine what numbers
are in the top half (relative to other the numbers) as the data continually
changes.

Thank you.
 
D

Domenic

I'm not sure if this is what you mean, but try the following...

Assumptions:

1) A2:A10 contains your labels

2) B2:B10 contains your numbers

3) AVERAGE is used to determine the top half relative to the other
numbers. You can change this to MEDIAN if you prefer.

4) Top half is considered any number that is greater than or equal to
the AVERAGE/MEDIAN. If you'd prefer 'greater than' only, change part of
the formula in D2 from (B2>=$C$2) to (B2>$C$2).

Formulas:

C2:

=AVERAGE(B2:B10)

D1: enter a 0 (zero)

D2, copied down:

=IF((A2<>"")*(B2>=$C$2),LOOKUP(9.99999999999999E+307,$D$1:D1)+1,"")

E1:

=LOOKUP(9.99999999999999E+307,D:D)

F2, copied down and over to the next column:

=IF(ROW()-ROW(F$2)+1<=$E$1,INDEX(A$2:A$10,MATCH(ROW()-ROW(F$2)+1,$D$2:$D$
10,0)),"")

Hope this helps!
 
Top