Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel 2010 - Find Bottom 10 Scores and Associated Names

Status
Not open for further replies.

caper11752

IS-IT--Management
May 6, 2008
26
CA
Hi Folks - I've done quite a bit of searching and have been unable to solve a problem I'm having. I have a list of 200 or so names and scores and need to find the bottom 10 values along with the names associated with them without using any code, only excel functions. There may be duplicate scores and a name may appear in the bottom 10 more than once. Here is an example of my data and what I'm trying to accomplish:

A B C D
1 Name Score Name Bottom 10
2 John 90.67 Alex 54.50
3 John 91.89 Susan 54.50
4 Susan 84.00 Alex 78.40
5 Susan 84.44 Jason 78.89
6 Susan 54.50 Jason 83.33
7 Mike 98.00 Tracey 85.56
8 Alex 85.00 Jason 86.00
9 Alex 90.49 Tracey 87.50
10 Alex 54.50 Tim 88.00
11 Alex 93.56 Tim 94.00
12 Alex 78.40
13 Tim 88.00
14 Tim 94.00
15 Jason 86.00
16 Jason 78.89
17 Jason 83.33
18 Kim 86.80
19 Tracey 87.50
20 Tracey 85.56

Columns A and B contain contain my data, Names and Scores.
Columns C and D show what the result should be with the lowest score in cell D2, the next lowest in D3, and so on.

I found several examples find top 10, but not for finding bottom 10.
 
Try the following array-entered formula in cell C2 for the names. It may be copied down.
=INDEX(A$2:A$20,MATCH(SMALL(B$2:B$20+ROW(B$2:B$20)/1048576,ROWS(C$2:C2)),B$2:B$20+ROW(B$2:B$20)/1048576,0))

To array-enter a formula, click in the formula bar, hold the Control and Shift keys down, then hit Enter. Release all three keys. Excel should respond by adding curly braces { }. You'll see #N/A! or #NUM! if you don't succeed.

Note that the reference to row 20 must be to the last cell containing a value. The formula will return the wrong result if the range includes blank cells

To get the corresponding low scores, put the following formula in cell D2. It may be copied down, and does not need to be array entered.
=SMALL(B:B,ROWS(D$2:D2))
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top