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


--
fap87
------------------------------------------------------------------------
fap87's Profile: http://www.thecodecage.com/forumz/member.php?userid=214
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=81781

try this
Sub ClearBottom5()
'Rows("12:14").Copy Rows(2)

Ln = 5
For r = 2 To 4 'lastrow
rln = Application.Small(Rows(r), Ln)
For i = 2 To Cells(r, Columns.Count).End(xlToLeft).Column
If Cells(r, i) <= rln Then Cells(r, i).ClearContents
Next i
Next r
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"fap87" wrote in message
...

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


--
fap87
------------------------------------------------------------------------
fap87's Profile: http://www.thecodecage.com/forumz/member.php?userid=214
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=81781

Scripts are definately more powerful but a simple way that is more work is to
sort each student. The quizes would get out of order but if you only cared
about the total it would put all the quizes in desending or ascending order
and you could sum the best 20 for each. There may be shortcuts to repeating
sorting I don't know about. You dont want to sort them all together or it
may apply the order for one to all the others.

"Don Guillett" wrote:

try this
Sub ClearBottom5()
'Rows("12:14").Copy Rows(2)

Ln = 5
For r = 2 To 4 'lastrow
rln = Application.Small(Rows(r), Ln)
For i = 2 To Cells(r, Columns.Count).End(xlToLeft).Column
If Cells(r, i) <= rln Then Cells(r, i).ClearContents
Next i
Next r
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"fap87" wrote in message
...

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


--
fap87
------------------------------------------------------------------------
fap87's Profile: http://www.thecodecage.com/forumz/member.php?userid=214
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=81781

A third approach would be to use the LARGE function to to report the hightest
20 grades. Like the sorting suggestion, this would just give you the highest
scores without reference to which exams they came from.

Jerry

"fap87" wrote:


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


--
fap87
------------------------------------------------------------------------
fap87's Profile: http://www.thecodecage.com/forumz/member.php?userid=214
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=81781