# Thread: Conditional Formatting with colours - tied values

1. ## Conditional Formatting with colours - tied values

I am preparing a spreadsheet to capture the scores of players in a card game. For the sake of example, assume there are 8 to 12 players and up to 28 hands to be played with each player receiving a score for each hand. At the end of it all, I will enter in each players score for each hand and run the various summary totals for each hand to decide the winner - one with the highest score. The nature of the game is such that there could be more than one player tied for "top score". I want to be able to highlight in red, or some other criteria, the situation where there are multiple players tied for top spot as there could be 2, 3 or more players in this situation depending on the outcome of each hand. So far, I have been able to apply conditional formatting to the total column in such a way that it will highlight the top score, but it will not highlight tied scores, i.e., the case where more than one player has a "top" score. My criteria that I am using is "=max(a2:a13)" and this only highlights the first one it finds. I am assuming there must be a way to highlight "tied scores", but I do not seem to be able to figure it out. Any help or insight that anyone would care to offer will be greatly appreciated. Thanks in advance.

Ron M

2. The precise description of the Conditional Formatting you need is as follows:

In the first box: Cell value Is
In the second box: equal to
In the third box: =MAX(\$A\$2:\$A\$13)

Note the use of \$ so that the range remains "absolute" and not "relative" to each cell which has this Conditional Format.

However, it would be better to give the range A2:A13 a meaningful Range Name such as Scores, whereupon the third box becomes =MAX(Scores)

3. Just for fun (?) if you use the following conditional format (in A2 then copy down), the maximum scores will only be highlighted if there is a tie !

Formula is: =AND(A2=MAX(\$A\$2:\$A\$13),SUMPRODUCT((\$A\$2:\$A\$13)*(\$A\$2:\$A\$13=MAX(\$A\$2:\$A\$13)))/MAX(\$A\$2:\$A\$13)>1)

This is easier to read if, as in the previous post, you use a Named Range:

Formula is: =AND(A2=MAX(Scores),SUMPRODUCT((Scores)*(Scores=MA X(Scores)))/MAX(Scores)>1)

You could adapt this to have one colour to show the highest score when there is only one winner, and a different colour to highlight tied winners.

I've attached a sample: play with the scores to see what I mean.

4. Hi Ron M

Try:
In the Conditional Formatting. New rule: =\$A2=MAX(\$A\$2:\$A\$13)

Kevin

5. Thanks Martin and Kevin, I managed to get it to work. Martin, thanks for the example.

Ron M