Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Question 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. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    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. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    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.
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi Ron M

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

    Kevin

  5. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post
    Thanks Martin and Kevin, I managed to get it to work. Martin, thanks for the example.

    Ron M

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •