Conditional formatting to find

Folks,
I have a worksheet with a table of different numeric value. I am using conditional formatting with the Top/Bottom Rule choosing the top three. As I understand this the three largest values in a range will be found and shaded regardless of how many time they appear. From the image below I'm not getting the expected results
image cond formattingI've also attached the file
Conditional-formatting.xlsm
Frank FreeseAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
I only did it that way so that you can see what happens with different values in the table but you can simply apply the conditional formatting formula to your data - see this version attached

That's your data with my suggested CF formula as before - see that all values that are 88, 90 or 99 are formatted - if you change values the formatting will change

regards, barry
Conditional-formattingV2.xlsm
0
 
helpfinderIT ConsultantCommented:
Your conditional formatting should highlight TOP 3 (3 highest) values in your marked area - as I see from the printscreen 90, 90 and 99 are the highest values in your table
0
 
Frank FreeseAuthor Commented:
I see the same thing, however, I'm led to believe that three largest values in a range will be found and shaded regardless of how many time they appear. I was expecting to see 99, 90, and 88
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Frank FreeseAuthor Commented:
I suspect I need the to incorporate the LARGE function but I'm guessing. If correct, how?
0
 
barry houdiniCommented:
Assuming your values in A2:E10 then you can apply conditional formatting using the "use a formula to determine which cells to format" option with this formula

=SUM(IF(FREQUENCY(IF($A$2:$E$10>A2,$A$2:$E$10),$A$2:$E$10),1))<3

See attached example where I have entered random values in that range - press F9 to re-generate random values.

Top 3 values will be highlighted no matter how often they occur

regards, barry
conditional-formatting-top3.xlsx
0
 
Frank FreeseAuthor Commented:
Close - Barry
Now your code re-writes the table. Let's make an assumption that a value in an existing table was incorrectly entered. When the error was correct so that it became a top 3 the table should remain except it would have an additional cell highlight. How is that coded, please?
0
 
Frank FreeseAuthor Commented:
Barry,
That's what I needed - great job
0
 
Frank FreeseAuthor Commented:
thanks and nice job
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.