1. ## 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 (0-19=E, 20-39=D, 40-59=C, 60-79=B & 80-100=A). Grade A-C 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..
https://windowssecrets.com/forums/ima...s/confused.png

2. 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.

3. 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

4. ## The Following User Says Thank You to Maudibe For This Useful Post:

Nur Hessa (2015-05-01)

5. thanks for reply.. can i know how to upload ms excel file as a sample for my problem solution, tq.

6. 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.

7. ## sorting grade n marks

hi,

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(0-19) D(20-39) C(40-59) B(60-79) A(80-100)

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.

8. 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.

9. ## 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.

10. 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.

11. Hi RetiredGeek,

It's work. thank you so much for helping me on this. tq again.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•