Ranking based on criteria - help needed

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

2. Re: Ranking based on criteria - help needed

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

3. Re: Ranking based on criteria - help needed

Try this:

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

4. Originally Posted by Glenn Kennedy
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. 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.

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

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