Results 1 to 10 of 10
Thread: sorting grade n marks

20121029, 11:04 #1
 Join Date
 Oct 2012
 Location
 Kuala Lumpur, Malaysia
 Posts
 7
 Thanks
 1
 Thanked 0 Times in 0 Posts
sorting grade n marks
hi all,
i'm new at here, just want to know the function to sort student grade n marks for final exam.
basically there is 5 grade (A,B,C,D & E) and the range marks for grade is (019=E, 2039=D, 4059=C, 6079=B & 80100=A). Grade AC is PASS and Grade D&E is FAIL.
i already try make RANK function, but have problem when student FAIL one paper but got high marks, the student still at top rank, i want to make rank that the student should PASS all 5 paper n high marks will at the top rank n the student with one or more paper FAIL will automatically go down after the PASS student. tq for helping me..
http://windowssecrets.com/forums/ima...s/confused.png

20121029, 14:46 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,731
 Thanks
 395
 Thanked 1,533 Times in 1,390 Posts
Maliq,
Welcome to the Lounge as a new poster.
I see that English is not your first language and thus your explanation is a little hard to understand.
Could you possibly upload a sample workbook with your layout and some sample data.
On a second sheet in the workbook manually enter the data again to show how you want the data to look (how it should be sorted) when done.
Again welcome.May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20121029, 16:07 #3
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,754
 Thanks
 129
 Thanked 693 Times in 629 Posts
Like the Geek, I am not sure what you are driving at. However, it seems as though your problem would be resolved if instead of averaging the numeric grades then assigning a letter grade to the average, you assign a letter grade to each test then average the letters. What ever your preference, E (the lowest) could be a zero or a one:
ex. A=4, B=3, c=2, D=1, E=0
example.png
where tthe formula for column B is:
=IF(A4="","",IF(AND(A4>79,A4<=100),"A",IF(AND(A4>5 9,A4<=80),"B",IF(AND(A4>39,A4<=60),"C",IF(AND(A4>1 9,A4<=40),"D","E")))))
And the formula for column C is:
=IF(B4="","",IF(B4="A",4,IF(B4="B",3,IF(B4="C",2,I F(B4="D",1,0)))))
HTH,
Maud

The Following User Says Thank You to Maudibe For This Useful Post:
Nur Hessa (20150501)

20121030, 02:05 #4
 Join Date
 Oct 2012
 Location
 Kuala Lumpur, Malaysia
 Posts
 7
 Thanks
 1
 Thanked 0 Times in 0 Posts
thanks for reply.. can i know how to upload ms excel file as a sample for my problem solution, tq.

20121030, 13:04 #5
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,731
 Thanks
 395
 Thanked 1,533 Times in 1,390 Posts
Maliq,
Start your reply then click the Go Advanced button at the bottom right corner.
Then scrol down until ou see the Manage Attachments button and click it.
Then follow the dialog boxes.May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20121031, 02:30 #6
 Join Date
 Oct 2012
 Location
 Kuala Lumpur, Malaysia
 Posts
 7
 Thanks
 1
 Thanked 0 Times in 0 Posts
sorting grade n marks
hi,
i already upload ms excel file, i already keyin 5 example in spreadsheet,
1.ADAM 80(A) 89(A) 89(A) 87(A) 90(A)
2.BOB 99(A) 87(A) 88(A) 38(E) 99(A)
3.AHMAD 87(A) 68(B) 67(B) 64(B) 76(B)
4.ALI 45(C) 56(C) 48(C) 52(C) 56(C)
5.NUR 34(D) 35(D) 36(D) 21(D) 45(C)
the marks for PASS is 40 and above n below 40 is FAIL.
the range is E(019) D(2039) C(4059) B(6079) A(80100)
A,B and C is PASS & D and E is FAIL.
With the above result, ADAM is rank no 1, AHMAD is rank no 2, ALI is rank no 3, BOB is rank 4 and NUR is rank no 5. Even BOB marks is higher than AHMAD n ALI because BOB FAIL 1 paper, his ranking should be go to down.
I want student with all PASS marks first in rank n then go with 1 or more FAIL marks. tq again for helping me.

20121031, 09:48 #7
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,731
 Thanks
 395
 Thanked 1,533 Times in 1,390 Posts
Maliq,
If I understand you correctly this should work.
In col Q put the formula: =COUNTIF(C5:L5,">39")*100+N5
This will calculate the number of passing grades and multiply it by 100 and then add the calculated "JUMLAH"
In col O change the formula to: =RANK(Q5,$Q$5:$Q$46,0)
This will rank those w/o any failing grades at the top, those with 1 failing grade next, those with 2 failing grades next, etc. Of course within each group they will be ranked by their calculated percentage. I hope this is what you were after.
Of course once set up you can hide Col Q if you wish.May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20121031, 19:21 #8
 Join Date
 Oct 2012
 Location
 Kuala Lumpur, Malaysia
 Posts
 7
 Thanks
 1
 Thanked 0 Times in 0 Posts
sorting result by GRADE
Hi RetiredGeek,
It's work. thank you so much for helping me on this. I have one more question.
How to make student with More A's on top n less A's or other grade on below.
For example:
ABU 79 B 79 B 79 B 79 B
BURN 80 A 80 A 80 A 60 B
MIKE 80 A 80 A 80 A 80 A
ALI 41 C 41 C 41 C 39 D
AHMAD 41 C 41 C 41 C 41 C
JOHN 100 A 34 D 100 A 100 A
How to make BURN to be in 2nd ranking not ABU. BURN got 3A's n 1B and ABU got 4B. In this spreadsheet, its calculate based on total marks. tq again for helping me. i already attach the excel file.tq so much.

20121101, 09:25 #9
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,731
 Thanks
 395
 Thanked 1,533 Times in 1,390 Posts
Maliq,
Ok you just need to add extra points for each of the passing letter grades, i.e. A=400, B=300, C=200.
Change the formula in Col O to:
=COUNTIF(C7:J7,">39")*1000+L7+(COUNTIF(C7:J7,"A")* 400)+(COUNTIF(C7:J7,"B")*300)+(COUNTIF(C7:J7,"C")* 200)
Rankings.PNG
I hope this helps.
P.S. You should also change the formula for calculating the letter grade to:
=IF(C7>0,LOOKUP(C7,$X$5:$Y$10)," ")
Also, the worksheet you posted had the grading table sorted out of order!
I'd recommend that you move the grading table to a second worksheet in the workbook so when you sort you don't have to worry about it.Last edited by RetiredGeek; 20121101 at 09:43.
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20121108, 18:49 #10
 Join Date
 Oct 2012
 Location
 Kuala Lumpur, Malaysia
 Posts
 7
 Thanks
 1
 Thanked 0 Times in 0 Posts
Hi RetiredGeek,
It's work. thank you so much for helping me on this. tq again.