+ Reply to Thread
Results 1 to 3 of 3

How to handle ties with conditional formatting

  1. #1
    Registered User
    Join Date
    10-11-2015
    Location
    Georgia
    MS-Off Ver
    2013
    Posts
    2

    How to handle ties with conditional formatting

    I have a column of numbers as shown below and need to highlight the 1st through 3rd place scores in red and the 4th through 6th place scores in yellow. I am using a formula with conditional formatting to accomplish this. However, the formula is viewing the scores of 89 as a tie and counting them as 5th and 6th place. I want both scores of 89 to be 5th place and the score of 88 to be 6th place.

    100
    95
    94
    93
    89
    89
    88
    87

    This is the formula I'm using for first place. I have a similar rule for 2nd through 6th.
    =LARGE($A$1:$A$25,1)

    Thanks for your help!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to handle ties with conditional formatting

    Hi, welcome to the forum

    I used this rule in CF...
    =RANK(F1,IF(COUNTIF($F$1:F1,F1)=1,$F$1:$F$8,0))<=3
    Make sure this is teh 1st rule - move it if needed
    Then change the 3 to 6

    However, I cant think, off the top of my head - without using a helper - , how to skip the actual 6th place, because it is a duplicate, and make the 7th place now be the 6th place
    (using a helper would be simple)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-11-2015
    Location
    Georgia
    MS-Off Ver
    2013
    Posts
    2

    Re: How to handle ties with conditional formatting

    Thank you Ford. Can you explain how to use a helper in this situation?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Top three values using conditional formatting without ties.
    By kprusko in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-03-2020, 11:57 AM
  2. Stop Fill Handle Formatting
    By Lucrin in forum Excel General
    Replies: 0
    Last Post: 11-05-2013, 02:16 PM
  3. multiple criteria conditional ranking with ties
    By samktlim in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-01-2013, 09:00 PM
  4. Replies: 5
    Last Post: 07-24-2012, 01:03 PM
  5. Replies: 1
    Last Post: 02-09-2010, 10:58 PM
  6. How to handle this Date formatting in Excel
    By jacksonmathews in forum Excel General
    Replies: 2
    Last Post: 07-10-2009, 02:33 AM
  7. Conditional Formatting Bottom 10 with "ties"
    By Dan in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-27-2006, 02:20 PM
  8. Conditional Formatting Bottom 10 with "ties"
    By dan in forum Excel General
    Replies: 1
    Last Post: 05-26-2006, 05:25 PM

Bookmarks

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