rank based on more than 1 column when ties occur

[FreakyGirl]

hello once again!

PlayerA & PlayerB have the same score of 40. However, even thou PlayerB should be higher based on other values in other columns, PlayerA will be ranked higher because A comes before B. I need the default secondary condition to be based on values in other columns.

ranking order in case of a tie should be as follows: ColumnB(score1) then ColumnC(average) then ColumnE(score2) then ColumnF(games it took to make the score in ColumnE).

columnB & columnC are based on the best 6 games from the score in columnE.

Currently im using
=IF(ISERR(RANK(B2,$B$2:$B$200,0)+COUNTIF($B$2:B2,B2)-1),"",RANK(B2,$B$2:$B$200,0)+COUNTIF($B$2:B2,B2)-1) to help cover the error. works fine as is except for what i stated above taking A over B.

I tried on my own the following:
=RANK(B2,$B$2:$B$200)+SUMPRODUCT(--($B$2:$B$200=B2),--($E$2:$E$200>E2)) & all i get are #####.

also tried:
=SUMPRODUCT((E2=$E$2:$E$200)*(B2<$B$2:$B$200))+1
& all i got was a 1 in every column

also tried:
=SUMPRODUCT((B$2:B$200=B2)*(E$2:E$200=E2)*(F$2:F$200>F2))+1
& all i got was 1 2 3 4 all over the column.

please help!

FreakyGirl

 

[Luke M]

Hi FreakyGirl,
Could you upload an example workbook, so we can see what numbers/layout you are dealing with?

 

[FreakyGirl]

just a side note...

I have a helper column that tells me what the 6 highest scores were per player.
=LARGE(G2:R2,1)&", "&LARGE(G2:R2,2)&", "&LARGE(G2:R2,3)&", "&LARGE(G2:R2,4)&", "&LARGE(G2:R2,5)&", "&LARGE(G2:R2,6)&""

IMO, 8,8,8,7,7,6 would be higher than someone with a 7,7,7,7,6,6 even thou they would have the same score. 8 is a perfect score on any given night so the more 8s you have the better.

Since 2 players can have the same score & average because i only take the 6 highest games, is it possible to use whoever has the most 8s in those 6 games to help decide who is higher ranked?

So ranking would be like below in cae of a tie:

ColumnB(score1) then ColumnC(average). if still a tie, then somehow use the LARGE formula posted above to consider the player who has the most 8's then the most 7's then 6's etc...

thanks so much & have a great day everyone!

FreakyGirl

 

[FreakyGirl]

i been working on this for the past couple of hours trying to figure it out & upon further review i may need a diff formula than what i thought. see new file.

its been sorted based on score in columnB & then column E & then column T.
the player ranked 2 & 3 have the same score & the same average but player ranked 2 has more points for the same amount of games played so they should be ranked higher & they are. same with player ranked 4 & 5. i was able to achieve this thru data then sort which was part of my problem earlier.

however based on that same principal, when players who are tied dont have the same # of games played this will be a BIG problem.

consider this:
the top 6 scores for each player is whats counted since not everyone can make all games.

the players ranked 9, 10, 11 & 12 are tied with 34. A9:A12 - but they all didnt play the same amount of games F9:F12 so their score in column E9:E12 cant be considered for ranking. player 12 has more 8's than the other 3 with the same score so he should be ranked 9th because hes a better player based on the num of times he got 8 which is a perfect score.

talk about a mindboggler! lol anyone have any ideas?

thanks so much!

FreakyGirl

 

[NARAYANK991]

Hi ,

I think you are approaching the problem in the wrong way ; by describing your thought processes in trying to solve the problem , I doubt that you are helping us understand the problem or your requirement.

Let us strip the problem of all but the facts / data. With reference to your tab named template6 :

1. Player name - column D

2. Row 1 - header giving the dates on which games were held

3. Cells G2:R83 - scores obtained by each player for every game that they played ; I do not know what is the significance of a score of 0.

4. Total score - Column E

5. Games Played - Column F - I do not know why you are multiplying the number of scores by 2.

The higher the score the higher the ranking.

Now , if you can do the following , it will help :

1. Clarify / confirm what is mentioned in (1) through (5)

2. Indicate :

a. What is the final output required from this exercise ?

b. What is the process by which this final output can be obtained ? Given the data in this file that you have uploaded , what would you manually ( not using Excel ) do to get the final output ?

Narayan

 

[FreakyGirl]

Hello
Thanks for your advice. To answer your questions:

1. Yes, those are player names. (ColumnD)
2. & 3. For each date in the header - a player can receive a score of 0-8 only. (ColumnG2:R2).
4. A total score of all games played so far. (ColumnE) Even after a player has qualified a running total is still kept but is not used unless there is a tie in the ranking in (ColumnA)
5. The number of games a player has played. (ColumnF). Each night is multiplied by 2 as each night consists of 2 games (0-4) totaled. Score can be 0 but never be more than 8.

A player can play up to 12 times a month but must play at least 6 dates to qualify for ranking.
Since not every player can make all the dates available only a players top 6 scores are counted no matter how many they play.

Other Columns not mentioned above:
ColumnC - a players average based on the 6 highest scoring nights.
ColumnS - a helper column - returns same value as (ColumnB) & is used for conditional only.
ColumnT - the 6 largest scores of a player seperated by a comma. To decide a tie between 2 players, whoever has more 8s would be ranked higher.

example: 8,8,7,6,5,5 is better than 8,7,7,6,6,5 even thou they both add up to 39.

When 6 dates have been played the scores will highlight thru conditional formatting to show which ones are being used for ranking. It will always be the Highest numbers with the conditional adjusting as scores are entered on each date.

Once a player has qualified with the 6 dates those scores are added for a sum (ColumnB) & each player ranked according to that sum. (ColumnA).

What i would like to do is have the ranking be decided in the following order in case of a tie in (ColumnB):

If a tie in (ColumnB) then look to (ColumnF-games) to see if the players in a tie have played the same number of games.
If Yes, then compare the values in (ColumnE-scores) to see which is greater. The player with the greater value should be ranked higher. If the values are the same then (ColumnT-top6scores) will need to be considered. I dont believe there can be a tie past this point.
If No then then (ColumnT-top6scores) will need to be considered.

Also please refer to the updated book that has a 2nd sheet in it. Its easier to read as it only shows the players who have made the 6 required nights.

I hope i have explained it better & ty again for your time.
FreakyGirl

 

[NARAYANK991]

Hi ,

See your file now ; I have added several helper columns to understand the issues involved. See if the logic is correct.

Narayan

 

[FreakyGirl]

hello
i was quite surprised when i opened the book. i have never seen or worked with a pivot table before.

yes the logic is correct - except - ties are still showing up after Final Rank. I didnt think there would be so unfortunately i need to use more criteria to break them.

Seeing how you did the sheet gave me the idea of comparing the tied players highest values from 8-0 down the list. Most 8's then most 7's then most 6's etc until the tie is broken. The player with the better number is ranked higher. Since this chart is based on how well a player can play it makes the most sence to continue using it to break ties.

I sorted the sheet & colored the rows with a problem for easier understanding.

the rows that are pink & green are correct.

the rows in red are also correct & i think do what im asking above. compare all the numbers down the line which is why the blue rows still show a tie.

the blue row is correct except for that darn tie even after comparing all the numbers. Since all of their higher numbers are exactly the same then check to see if they have played the same number of total games(ColumnF).

If TRUE then compare the values in (ColumnE) to see if they are the same. the Greater of the 2 values would then be ranked higher. For this example 49 points beats 46 points for the same number of games so that player would be ranked higher.
If they do not have the same value in (ColumnF) then color those 2 players yellow. Ill break it thru gaming.

The orange rows are a problem. 4 players with the same score but 2 of them say higher when only one of them should. the player with the most 8s should be the only player who says higher.

i hope i explained it & thank you for your help. This has turned out to be much harder than i thought.

FreakyGirl

 

[shrivallabha]

Just to throw in one more idea, a weightage system can be written as like below in cell U1:
=B1*100000000000+CEILING(24/F1,0.01)*10000000000+E1*1000000+SUBSTITUTE(T1,", ","")+0

Then in V1 write simply:
=RANK(U1,$U$1:$U$19)

This builds up a number which keeps all priorities in sequence and they come into play only in case of tie. I've had encountered a pretty similar situation at MrExcel some time back where it was school grades. Here I have explained my logic as well. Might prove useful in your case as well:
http://www.mrexcel.com/forum/excel-...ect-among-five-subject-various-condition.html

Note: I have assumed player with less games to be more prolific and should get more weightage. If it is other way round then just change 24/F1 to F1/24.

 

[NARAYANK991]

Hi ,

I will repeat what I said earlier ; first let us describe the logic in plain English.

1. The first rule is that any player will be considered for ranking purposes if they have played 6 or more games. Is this correct ?

2. If a player is considered for ranking purposes , we take the total of their top 6 scores ; suppose we call this SUMTOP6.

3. We rank all players according to their SUMTOP6 score ; the person with a higher SUMTOP6 score is ranked higher.

4. If two or more players have the same SUMTOP6 score , then what is to be done ? In your earlier post , you had mentioned that we should look at which player has the higher maximum score ; thus the player who has a top score of 8 will be ranked higher than a player whose top score is a 7 or less.

5. What if two or more players have the same SUMTOP6 score and the same maximum score ? Then , again according to your earlier post , the player with the higher number of maximum scores would be ranked higher ; thus if a player had two scores of 8 , they would be ranked higher than a player who had only one score of 8.

6. What if two or more players have the same SUMTOP6 score , the same maximum score , and the same number of maximum scores ? What is to be done in this case ?

Can you clarify / confirm each of these points ?

Narayan

 

[FreakyGirl]

hello Narayan
ty for your reply. im sorry, i thought i did. to answer your questions..

1. correct
2. correct
3. correct
4. correct
5. correct. keep comparing the max value until 1 player is better. 8,7,6,5,4,4 is better than 8,7,6,5,4,3
6. this is the case for the 2 blue rows. they both have the exact same numbers to make SUMTOP6. 8,7,7,6,6,6,5. when this happens (ColumnF) should be looked at to see if they have played the same number of total games.

if TRUE, then look at their total scores (ColumnE) & the higher score for same number of games would be ranked higher. In this example 49 beats 46 for same # of games.

If (ColumnF) does not have the same number of games for the tied players, then color it yellow. Theres nothing else i can use to break the tie. i just hope this doesnt happen often lol
---------------------

hello shrivallabha

ty for your solution. i have also checked out the link & im trying to understand it all. it does seem like its whats im trying to do.

thanks for the help both of you
FreakyGirl

 

[NARAYANK991]

Hi ,

Please confirm whether Shrivallabha's solution works. If it does not , only then we need to look at an alternative.

Narayan

 

[FreakyGirl]

hello again
yes it works - to a point. i had to adjust the ranking to only rank those in a tie. i showed what happens in (ColumnAB) if i use your way. I dont know how to combine both our ways so the formula is in each cell but only ranks the tie. i tried =IF(Y2:Y19="Tie","=RANK(Z2,$Z$2:$Z$19)",0) in AA2 but thats not it.

i uploaded updated file.

On that note, if there are only 2 players in a tie like in (ColumnY) like the blue rows then its shows whos higher & its correct (ColumnAA).

However, the orange rows are still wrong but closer to being right with the formula used in (ColumnAA).

Problem with orange rows in (ColumnAA) is 1st & 2nd are ranked correct but 3rd & 4th should be switched. (S11) has a higher max score value then (S10) so should be ranked higher.

Thanks again
FreakyGirl

 

[shrivallabha]

I see that the column B in your attached file is Empty. It should not be as it is used as criteria 1 for separating the player.

To reiterate, this is what my formula is doing:
1. Make Column B as the first criterion. Higher B higher the player would be ranked.

2. In case of tie in Column B it looks for number of matches played in Column F. I assume that the maximum number that would reflect there is 24. So 24/(no of matches) will give higher ranking since he'd has played less matches to get there is more prolific!

3. If the tie doesn't break there then it looks at total points amassed by the player in Column E. Higher E then Higher the player would be placed.

4. Finally, you used substitute for a modified file. So the situation is bit different. Here you've used count of MAX scores. The old string would still be useful as it compares 6 scores serially.

I am attaching the file I had worked on initially. Please see the sheet "template6 (2)" and refer to formulas in Column U and Column V.

Please check the rankings there and suggest where the results are not per your requirement. I'd imagine that it is within ties that the formula results are not per your requirement. Please post back with your reasoning for those.

 

[FreakyGirl]

1. yes that is correct. Higher the number in B the higher the ranking.

2. originaly, that was what i had requested but in this case a player is not required to play all 24 games - only 12. so to consider someone better because of less games but same SUMTOP6 would be unfair. the numbers that are added to get the SUMTOP6 (ColumnT) should be considered before (ColumnF). compare the 8s then 7s the 6s etc... if all 6 numbers are tied then use (ColumnF)

see players ranked 2 & 3. the player ranked 3 should be higher as they have more 8's

3. (ColumnF) should have the same number of games in order to use scores from (ColumnE). If the same then Higher in (ColumnE) would be ranked higher. If not the same in (ColumnF) then color it yellow.

see players ranked 4 & 5. they have the same numbers in (ColumnT) so (ColumnF) is checked which is the same so then (ColumnE) is used to break the tie.

however the players ranked 9, 10, 11 & 12 all have the same SUMTOP6 so in looking to (ColumnT) 9 & 10 are ranked correctly but 11 & 12 need to be switched because player ranked 12 has more 7's then player ranked 11.

4. im not sure what exactly you are doing with substitute & a modified file but using the count of MAX scores is correct because i want to compare those numbers in (ColumnT) serially.

sorta what Narayan did with the July.xls sheet in answer to one of my posts with the MAX.

Thanks to both of you for your help.
FreakyGirl

 

[shrivallabha]

Now it is getting clearer. Could you explain me which one is the better score?

1. racing_2425 ----- 776554
2. GAG_SweetTaz ---- 666655
3. sirlunchalot ---- 886552
4. SoWicked -------- 876544

e.g. 3 > 4 > 1 > 2?

 

[FreakyGirl]

yes you are correct.

3 is better than 4 because 8, 8 is better than 8, 7
4 is better than 1 because 8, 7 is better than 7, 7
1 is better than 2 because 7, 7 is better than 6, 6

if any of those numbers are the same, it would just keep comparing until one number is better. 5,5,5,5,5,5 is better than 5,5,5,5,5,4

if they are all the same numbers then look to (ColumnF) etc..

FreakyGirl

 

[shrivallabha]

Could you check the ranking in the attached file please?

Refer Column U,V for working out basis and then W for Rank.

 

[FreakyGirl]

hello
sorry for the delay in responding but its been crazy here with kids going back to school in a week! so much to do & no time to myself..

i have looked at the file & it is now ranking correctly thanks. however upon testing & playing with the numbers i realized there can still be a tie in (ColumnW) which can cause the new ranking to still rank with dups! arrgghhhh lol

To explain what i mean easily, look at the updated workbook & look at S5 - its colored pink. If you remove the 3 from that cell that will make the tie appear. Since the average is based on how well someone does individually it doesnt matter if they have the same number of games as the other player in a tie. the average will pull out the more prolific player as you stated earlier.

please note that the average is now column F & totalgames is moved to column G.

i tried to modify your formula in (ColumnW) to use the average of all the games played but i think im going about it the wrong way. i need the same formula as in (ColumnW) but it should be <> instead of = for ColumnG since it should say If the totalgames is not the same then refer to column F.

i tried that way & a few diff ways but i could never come up with the right syntax. right now even thou im using <> its giving me the same value as using = !!

thanks for your time
FreakyGirl

 

[shrivallabha]

That just is addition of third criterion to the logic. Their positioning is as per priority. P1 > P2 > P3 where P denotes Priority and number denotes level.

Take a look at column X and Z in the attached file. Z should give you the ranking per your latest requirement.

 

[FreakyGirl]

thanks so much!! this works like a charm now. thanks for all your time & energy you put into helping with this. its people like you who make this site truely awesome!

happy labor day to all
FreakyGirl

 

[shrivallabha]

Good to know that!

While working on the MrExcel situation I realized that with any number of priorities, if you arrange in sequence so that one doesn't overlap the other finding the rank becomes easier. If a condition is non-numerical then we just need some way to make it numerical e.g. the sequence of 6 best scores.