How to clear the lowest 10 values from a list of 30 values

Suppose I have a database of 50 students. Each row starts with the name of the student and then marks of 30 quizes for each student. e.g.-

Jack- 10 7 8 5 10 9 8 7 3 6 4 7 3 ........
Millie- 2 3 10 9 8 2 7 1 2 9 2 0 7 9.......
Ryan- 4 5 7 8 9 10 2 4 5 7 8 9............
...............................................................
..............................................................
Now I want to count only best 20 quiz marks out of 30 quizes and show the cells containing 10 lowest values as blank.
e.g. if i want to clear lowest 5 values for jack-
Jack- 10 7 8 _ 10 9 8 7 _ _ _ 7 _ ....

Now how do I do this and how do I repeat it for all 50 students? That is, how do I show only best 20 quiz marks out of 30 for each student?


please help
fap87

Assuming Jack's grades are in the range A2:AE2 then use:

=IF(B2>=LARGE($B$2:$AE$2,20),B2,"")

and then copy this across (to the right) to represent each grade.

HTH

I would always resist the idea of making grades really disappear. That's the kind of thing you really want to keep, isn't it?

So, I would have two sheets, one with all the raw-original-untouched scores, and another that pulls from the raw data and shows you the top 20 scores.

Sample attached.

Originally Posted by ConneXionLost

Assuming Jack's grades are in the range A2:AE2 then use:

=IF(B2>=LARGE($B$2:$AE$2,20),B2,"")

That may bring over more than 20 grades.

shg,

Quite right; in the case of ties, there will be occasions where the formula won't discriminate between a 6 in column G or a 6 in column K. However, given the situation, I thought it best for the OP to decide if that was still an issue.