+ Reply to Thread
Results 1 to 5 of 5

Ranking based on criteria - help needed

  1. #1
    Registered User
    Join Date
    08-14-2009
    Location
    Lincoln, England
    MS-Off Ver
    MS Office 2019 Professional Plus
    Posts
    60

    Ranking based on criteria - help needed

    Hi All,

    I'm creating a tracking spreadsheet to track assessment data across 3 year groups in school. I've got most of it done ok, but there's one part I'm struggling with. I'd like to be able to rank students based on an average of their assessment scores. I've got something in there, but it's not working as well as I'd like because there may be times when a student will not have any data entered. This means that the actual 1st place student will not return a rank of 1. For example, if there are 10 students blank, the actual 1st place will return a value of 11. It's made slightly more complicated by the fact that students will be either studying a 'Core' scheme of work, or 'Support'. My ranking formula currently takes this into account. I've attached a sheet with example scores in (no names of course). This formula is repeated 3 times for Autumn, Spring and Summer terms.

    I've managed to hide most of the formulae so that they don't show up unless there is a numerical value there, but can't find a way to do it with the ranking formula. If anyone could help with this as well, I'd appreciate it, just to make it look a bit neater.

    Thanks so much to anyone who can help!

    Dom

    P.S. I know the sheet I'm attaching is bigger than 10-20 rows, but I think it's important to see the effect the ranking has. It's only 250 rows, so not huge.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Ranking based on criteria - help needed

    Are you still uisng Excel 2007? Please confirm and update your profile, if required.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2021
    Posts
    6,046

    Re: Ranking based on criteria - help needed

    Try this:

    =COUNTIFS($D$5:$D$250,D5,$O$5:$O$250,">"&O5)+1

  4. #4
    Registered User
    Join Date
    08-14-2009
    Location
    Lincoln, England
    MS-Off Ver
    MS Office 2019 Professional Plus
    Posts
    60
    Quote Originally Posted by Glenn Kennedy View Post
    Are you still uisng Excel 2007? Please confirm and update your profile, if required.
    No, I’m now using MS Office 2019 Professional Plus.

    Thanks for the reminder. Updated profile.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,987

    Re: Ranking based on criteria - help needed

    Please let us know if Phuocam's formula does what you want or if there is need of further assistance.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Replies: 1
    Last Post: 08-08-2019, 02:28 AM
  2. [SOLVED] Multiple Criteria Ranking Help needed
    By Rambluesky in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-26-2018, 09:42 AM
  3. Ranking based on subtotal sum and ignoring rows based on criteria
    By Crysaline in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-21-2018, 07:23 AM
  4. [SOLVED] Create a table based on top ranking criteria, based on multiple fields.
    By stevensimon10482 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-13-2017, 08:49 PM
  5. Ranking Question - Ranking based criteria but having forumla value issues
    By harp1983 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-09-2016, 07:03 AM
  6. Ranking Based on Criteria
    By mlbdc2012 in forum Excel Formulas & Functions
    Replies: 35
    Last Post: 02-26-2014, 01:07 PM
  7. Ranking based on certain criteria?
    By Ahmad Adha Ali in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-18-2005, 04:51 AM

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