|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 1st, 2009, 10:11 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
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 -- 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 
|
|
#3
April 3rd, 2009, 12:19 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
How to clear the lowest 10 values from a list of 30 values
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
|
|
#4
April 3rd, 2009, 07:40 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
How to clear the lowest 10 values from a list of 30 values
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
