# Multiple Criteria Ranking Help needed

1. ## Multiple Criteria Ranking Help needed

Hello, I have a problem with the ranking system.

Below are some examples of data.

 D E F Rank 46 3 3 45 5 3 44 4 12 42 2 9 56 1 17 64 3 17 23 3 17 64 5 17

D - Score
E - Selected Number
F - Group Number

I would like it to be ranked in group; meaning if it's in Group 17 (Column F), I want it to be ranked by, first, Seleceted Number (column E; from smallest to largest), and then by Score (column D, from lagrest to smallest)

For better understanding, look at it like a university application system where you can choose multiple university (F) in multiple ranks (E) with the scores (D)

I tried some various method with sumproduct, rank, countifs but sadly unsuccessful. So, I decided to ask for some help here.

Thank you in advance. X

2. ## Re: Multiple Criteria Ranking Help needed

Sawaddee

Please try at D2 and copy down

=SUMPRODUCT(--(C2*10^8-B2*1000+A2<\$C\$2:\$C\$9*10^8-\$B\$2:\$B\$9*1000+\$A\$2:\$A\$9))+1

If this not correct, please mock up manual answer at column D.

3. ## Re: Multiple Criteria Ranking Help needed

In D2 then cop down.

=SUMPRODUCT((\$C\$2:\$C\$9=\$C2)*(((\$B\$2:\$B\$9)+(\$A\$2:\$A\$9*10^-5))<(\$B2+\$A2*10^-5)))+1

4. ## Re: Multiple Criteria Ranking Help needed

Originally Posted by Bo_Ry
Sawaddee

Please try at D2 and copy down

=SUMPRODUCT(--(C2*10^8-B2*1000+A2<\$C\$2:\$C\$9*10^8-\$B\$2:\$B\$9*1000+\$A\$2:\$A\$9))+1

If this not correct, please mock up manual answer at column D.
Sawasdee Krub

Sorry to say that it is not exactly what I would like it to be.
The concept of ranking in D and E is correct but I need the ranking to be grouped by F first.
I illustrate the examples as you requested below because It seems that I still cannot attach any links and files.

 D E F Rank 56 1 Group A 1 64 3 Group A 2 23 3 Group A 3 64 5 Group A 4 46 3 Group B 1 45 5 Group B 2 44 4 Group C 1 42 2 Group D 1

5. ## Re: Multiple Criteria Ranking Help needed

Originally Posted by kvsrinivasamurthy
In D2 then cop down.

=SUMPRODUCT((\$C\$2:\$C\$9=\$C2)*(((\$B\$2:\$B\$9)+(\$A\$2:\$A\$9*10^-5))<(\$B2+\$A2*10^-5)))+1
This is nearly exactly what I would like it, but in the first column, it should be ranked from the largest number to the smallest number.

6. ## Re: Multiple Criteria Ranking Help needed

Then perhaps:

``Please Login or Register  to view this content.``
WBD

7. ## Re: Multiple Criteria Ranking Help needed

Please try D2

=SUMPRODUCT((\$C\$2:\$C\$9=C2)*((\$B\$2:\$B\$9*10^5-\$A\$2:\$A\$9)<B2*10^5-A2))+1

8. ## Re: Multiple Criteria Ranking Help needed

Originally Posted by WideBoyDixon
Then perhaps:

``Please Login or Register  to view this content.``
WBD
Originally Posted by Bo_Ry
Please try D2

=SUMPRODUCT((\$C\$2:\$C\$9=C2)*((\$B\$2:\$B\$9*10^5-\$A\$2:\$A\$9)<B2*10^5-A2))+1
Both of these works perfectly, Thank you so much! X

9. ## Re: Multiple Criteria Ranking Help needed

Formula revised

=SUMPRODUCT((\$C\$2:\$C\$9=\$C2)*(((\$B\$2:\$B\$9)-(\$A\$2:\$A\$9*10^-5))<(\$B2-\$A2*10^-5)))+1

#### Thread Information

##### Users Browsing this Thread

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