+ Reply to Thread
Results 1 to 6 of 6

Student ranking based on pass or fail and average grade (multiple condition)

  1. #1
    Registered User
    Join Date
    11-09-2014
    Location
    malaysia
    MS-Off Ver
    2010
    Posts
    4

    Student ranking based on pass or fail and average grade (multiple condition)

    Hi,

    I am in progress to build offline exam marksheet for school. I want to rank students based on first, considering PASS or FAIL, then looking at average grade.
    Group of Pass student will be on upper ranking then group of Fail student will be lower ranking. Then,for average grade, the minimum value will be upper ranking.

    Desire output:-

    RANK.png

    Current formula:-
    =IF(AK13="PASS",SUMPRODUCT(--(AK13=$AK$13:$AK$53),--(AJ13<=$AJ$13:$AJ$53)))+IF(AK13="FAIL",SUMPRODUCT(--(AK13=$AK$13:$AK$53),--(AJ13<=$AJ$13:$AJ$53)))

    Problems:-
    1. When I use the above formula, the output will be divided by group of Pass and Fail students but the problem is for the Fail student it will started with 1 again which is not continuous from previous list.
    2. Then, when we want to setup the formula to other cells, it becomes error for the entire columns.
    3. Let's say, Grade 1 have 3 classes, how do I rank students together between other class?

    Here, together I attached the workbook.
    I hope members willing to help me to solve these problems please. Thanks a lot
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Student ranking based on pass or fail and average grade (multiple condition)

    Hi Zalya

    First add IFERROR Function to AJ Column then Column AL paste the below function

    Please Login or Register  to view this content.
    Thanks - Naveed
    -----------------------------
    If the suggestion helps you, then Click * to Add Reputation
    To Attach File: Go Advanced>>Manage Attachments>>Add Files (In Top Right Corner)>>SelectFiles>>.........Locate Your File(s)>>Upload Files>>Done (In Bottom Right)
    1. Use [code] code tags [\code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,816

    Re: Student ranking based on pass or fail and average grade (multiple condition)

    First as Naveed said add IFERROR Function to AJ Column then Column AL paste the below function
    =SUMPRODUCT(((($AK$13:$AK$53="Pass")*($AJ$13:$AJ$53)+($AK$13:$AK$53="Fail")*($AJ$13:$AJ$53/100))>$AJ13/IF(AK13="Pass",1,100))*1)+1
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Registered User
    Join Date
    11-09-2014
    Location
    malaysia
    MS-Off Ver
    2010
    Posts
    4

    Re: Student ranking based on pass or fail and average grade (multiple condition)

    Thanks to Naveed and nflsales for your feedback and help.
    I've try your suggestion but I still need a solution for ranking.

    Naveed suggestion to use same formula for AL column.
    1.png

    nflsales suggestion formula for AL column.
    2.png

    Hopes your help and kindness

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,816

    Re: Student ranking based on pass or fail and average grade (multiple condition)

    see attached file
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-09-2014
    Location
    malaysia
    MS-Off Ver
    2010
    Posts
    4

    Re: Student ranking based on pass or fail and average grade (multiple condition)

    Great!! Thanks nflsales.
    Last edited by zalya; 11-24-2014 at 02:55 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. SUM Time Then Pass/Fail Based On Given Parameters
    By Twizik in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-29-2014, 10:46 AM
  2. [SOLVED] Need a formula for pass/fail based on no. of Y answers but automatically fail if 3a is N
    By Researcher1111 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-15-2013, 10:20 PM
  3. Need a formula to Pass/Fail data entered based on several variables....
    By smurf0617 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-21-2013, 01:10 PM
  4. Student grade inputting and grade percentage calculations
    By confusedteacher in forum Excel General
    Replies: 2
    Last Post: 10-23-2011, 07:50 PM
  5. Creating a PASS or Fail Condition - School Schedules
    By phanuelmas in forum Excel General
    Replies: 5
    Last Post: 07-21-2010, 04:51 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1