Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Oct 2012
    Location
    Kuala Lumpur, Malaysia
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Unhappy 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..
    http://windowssecrets.com/forums/ima...s/confused.png

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 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

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 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

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

    Nur Hessa (2015-05-01)

  5. #4
    New Lounger
    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.

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 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

  7. #6
    New Lounger
    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 key-in 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(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.
    Attached Files Attached Files

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 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.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #8
    New Lounger
    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.
    Attached Files Attached Files

  10. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 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.
    Attached Files Attached Files
    Last edited by RetiredGeek; 2012-11-01 at 10:43.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #10
    New Lounger
    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.

Posting Permissions

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