Excel Excel 2007 home and student

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 68You'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:

=B2+ROW()*0.00000001

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

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

=SMALL($C$2:$C$10,ROW()-1)

This will put the grades in numerical order.

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

=IF(D2<50,INDEX($A$2:$A$10,MATCH(D2,$C$2:$C$10,0),1),"")

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!)

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History