Give Ranks depending on score AND people wishes

Question

I have a Ranking problem that I cannot seem to find a solution for.

I have 3 prices to give to 3 people. Each one performed a test to get a rank. But each participant can also wish for a prize.

Candidate	Test_Rank	Wished_Rank
Georges	2	1
Mark	1	3
Clara	3	NA

In this example I want to attribute Ranks to the participants by taking into account the Wished_Ranks, only when Wished_Rank is bigger than Test_Rank. For example here the result I want is:

Candidate	Column B
Georges	1
Mark	3
Clara	2

Answer 1

Here is a solution to your problem. One problem with the example in the question is that it's a bit simple with only 3 candidates. There are interesting questions for what happens in the case of more candidates. For example, imagine there are 5 candidates, and the candidates with Test_Rank #1 and Test_Rank #2 both have a Wished_Rank of 5. Of course, Test_Rank #1 will receive their wish, but what happens with Test_Rank #2? Giving them Rank 1 just because Rank 5 was already taken would be very far away from their wish. It would seem that we should give that candidate the "next best rank" to their wish, i.e. the closest rank possible, Rank 4. I have taken this into account with my solution -- I hope that's what you were looking for.

There are some quite lengthy formulas involved, so I will try to break the solution down.

Here is an example like yours, but with 6 candidates. Columns A-C are just the same information as you gave in your example. Columns E-F are added columns required to get the final rankings, which are given in Column G (for the names in Column E).

	A	B	C	D	E	F	G
1	Candidates	Unsorted Test_Rank	Unsorted Wished_Rank		Sorted Candidates	Sorted Wished_Rank	Final_Rank
2	G	2	4		M	4	4
3	M	1	4		G	4	3
4	C	3	5		C	5	5
5	P	4	1		P	1	1
6	B	6	none		L	2	2
7	L	5	2		B	none	6

Creating Extra Columns E-F

These are both repeated lists of the candidate names and Wished_Rank that sort based on their test results. This is so that the process of assigning the final rankings can go row-by-row according to who deserves "first pick" of their final rank.

• E2 formula: =SORTBY(A2:A7,B2:B7)
• F2 formula: =SORTBY(C2:C7,B2:B7)

G2 Formula for the person with the best Test_Rank

This one is easy. G2=F2. The person whose Test_Rank was highest gets first pick.

G3-G6 Formulas for people who weren't the best or worst

Here is where things get a bit complex.

The first thing to do is to check if the Wished_Rank has already been taken by those who achieved better Test_Ranks. If it hasn't been taken, then this person can receive their desired rank. So the comparison will compare the Wished_Rank, to the rows above the current row in the Final Rank column, Column G.

Using F4 as an example, "C" received a Test_Rank of 3, so is allowed to pick 3rd. This lands "C" in row 4.

The list of ranks that have already been assigned go from G2 to the row above the current one being considered. This can be written as $G$2:INDEX(G:G, ROW()-1). F4 will be compared to this list. If it's in the list, a different-than-wished-rank will have to be given, and if it's not in the list, F4 can be granted.

So the formula in G4 for "C"'s final rank will look something like: =IF(SUMPRODUCT(--(F4 = $G$2:INDEX(G:G, ROW()-1))), different_than_wished_rank, F4)

Available Ranks if Wished_Rank is Taken

If the Wished_Rank has been taken, it is necessary to know what ranks are still available to be assigned. This can be achieved by comparing the list of ranks that have already been assigned with the list of all possible ranks. To do this, the UNIQUE function can be used on these values with the "Exactly_once" logical turned on, so that repeated values between the two lists are ignored.

• The list of all possible ranks are contained in column B, cells B2:B7
• As already used above, the list of ranks that have already been assigned are again $G$2:INDEX(G:G, ROW()-1)

For the UNIQUE function to work nicely, the two lists above need to be combined into one. This is done with the VSTACK function.

So the ranks still available to be assigned are: UNIQUE( VSTACK(B$2:B$7,$G$2:INDEX(G:G, ROW()-1)) ,,1)

Getting the Closest Rank to the Wished_Rank

As mentioned before, the best thing to do for the candidates whose Wished_Rank is not available, is to find the next closest rank to their wish out of the ranks that are still available.

This can be achieved with the INDEX and MATCH functions, like written here: https://www.extendoffice.com/documents/excel/1050-excel-find-closest-value.html

In this case the the range where you want to find the specific value is in the available ranks (described above), and the searching value that you want to be compared with is the Wished_Rank.

Again using G4 as an example, the formula would look something like this: INDEX( available_ranks ,MATCH(MIN(ABS(available_ranks-F4)),ABS(available_ranks-F4),0))

The "available_ranks" term above is simply equal to the formula worked out before in the previous section.

Note: If the two ranks on either side of the Wished_Rank are available, this formula picks the smaller one, i.e. closer to Rank 1.

Final Formula for G3 - G6

Combining everything above, for G3 you get this monster formula, which you can simply drag down to apply to G4, G5, and G6 / all other candidates who aren't the best or worst:

=IF(SUMPRODUCT(--(F3 = $G$2:INDEX(G:G, ROW()-1))), INDEX(UNIQUE(VSTACK(B$2:B$7,$G$2:INDEX(G:G, ROW()-1)),,1),MATCH(MIN(ABS(UNIQUE(VSTACK(B$2:B$7,$G$2:INDEX(G:G, ROW()-1)),,1)-F3)),ABS(UNIQUE(VSTACK(B$2:B$7,$G$2:INDEX(G:G, ROW()-1)),,1)-F3),0)), F3)

G7 Formula for the Candidate with the Lowest Test_Rank

The person with the lowest Test_Rank has no choice in what Final_Rank they get. So for this, the formula simply compares all possible ranks to the ones already given out. This is done with the same UNIQUE and VSTACK functions explained before.

=UNIQUE(VSTACK(B2:B7,$G$2:INDEX(G:G, ROW()-1)),,1)

Hope this all helps and solves the problem. It should be general for any number of candidates, including 3 like in the example in your question.