List all the least

Excel Excel 2007 home and student
May 7, 2010 at 06:26:55
Specs: Windows XP
How can I list all student names where grade<50 in excel?

See More: List all the least

May 7, 2010 at 20:59:17
If you could promise me that there will never be more than one student with the same grade, we can eliminate Step 2 below. If not, we need that step to force each grade to be different. I'll explain why later.

1 - Let's say you have this table:

      A            B
1    Name        Grade
2    Dave         10
3    Raul         20
4    Sue          55
5    LeLe         45
6    Achmed       67
7    Derek        27
8    Adèle        34
9    Biff         27
10   Sigmund      68

You'll notice that there is a tie between Derek & Biff.

2 - To eliminate the ties, enter this formula in C2 and drag it down to C10:


This will increase the grades just enough to eliminate the ties.

3 - Now, in D2, enter this formula and drag it down:


This will put the grades in numerical order.

4 - Finally, in E2, enter this and drag it down:


This will pull the names of those students whose grade is less than 50. Change a grade, and the list will update.

5 - You can hide Columns C and D, since we only need them for the formula. You can also put them someplace else, even on another sheet if that makes using your spreadsheet easier.

Explanation for Step 2:

Since we need MATCH to find the 2 different names associated with the two 27 grades, we need the "27's" to be slightly different. The "+ROW()*0.00000001" takes care of that.

The MATCH function finds each adjusted grade and the INDEX function uses the value returned by MATCH as the Row number to pull the name from Column A.

I hope that makes sense (and helps!)

Report •
Related Solutions

Ask Question