top twenty percent

[Backdoor Cover]

Thanks in advance:

I ran an in house sales tournament for my staff. it ran for 25 weeks. I have names across B-Z and their accumulated points from 2-95.

I would like a formula to pull out their top 15 sessions.

So if Bob has 12 15 31 13 43 25 23 12 58 33 22 32 42 12 9 12

I want to run a formula that would extract the top 15 of those. If possible.
It also has to do it for all 90 staff members otherwise I would have done it manually. I have tried everything I can think of and can't seem to figure it out. Im pretty foul with the Indirect function so I came here for help.

Thanks!

[BenjieLop]

Thanks in advance:

I ran an in house sales tournament for my staff. it ran for 25 weeks. I have names across B-Z and their accumulated points from 2-95.

I would like a formula to pull out their top 15 sessions.

So if Bob has 12 15 31 13 43 25 23 12 58 33 22 32 42 12 9 12

I want to run a formula that would extract the top 15 of those. If possible.
It also has to do it for all 90 staff members otherwise I would have done it manually. I have tried everything I can think of and can't seem to figure it out. Im pretty foul with the Indirect function so I came here for help.

Thanks!

This might work for you ...

=sum(large(B2:B95,row(1:15)))

This is an array entered formula (confirm using Ctrl+Shift+Enter keys INSTEAD of just the Enter key).

[olasa]

Here are a 2 alternatives:


A) Top 15 results for Each staff member
Sheet2, top left: (make a matrix)

....Bob.B......Amanda.C..............................****.Z
1..=LARGE($A2,Sheet1!B$2:B$95)
2
3
..
15
Copy the Rank-formula to every cell.


B) Top 15 results for All staff members
Highlight the top 15 points:
1. Put the cursor on the top left point in the list
2. Select Conditional formatting
3. Condition1: Select 'Formula is'
4. Type: =RANK(B2,$B$2:$Z$95)<=15
5. Format... Color: Red.
6. OK
7. Copy and Paste this format to every point in the list (Paste special...Formats)
Done
Now the top 15 points for All staff memebers should be highlighted.

Ola Sandstrom


Note:
It is also possible to use >15 and Color: White to hide the lowest scores.