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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Frank FreeseAuthor Commented:
I suspect I need the to incorporate the LARGE function but I'm guessing. If correct, how?
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

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
barry houdiniCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
Barry,
That's what I needed - great job
0
Frank FreeseAuthor Commented:
thanks and nice job
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.