Conditional Formatting issue. I'm doing an average of the top 8 scores out of 20. I need to highlight ONLY the 8 scores that are being averaged. I'm using Conditional Formatting using Top Rank and 8 and if the scores are mostly different then it works great. But if there are duplicate scores then it will highlight 9 or more scores depending on how many duplicates. Is there a formula solution in Conditional Formatting or any other way?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Highlight Top Ranked 8 out of 20 Values
- Thread starter andycap
- Start date
Fluff
MrExcel MVP, Moderator
- Joined
- Jun 12, 2014
- Messages
- 90,554
- Office Version
- 365
- Platform
- Windows
How about
| +Fluff 1.xlsm | ||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | |||
| 8 | 28 | 22 | 18 | 23 | 9 | 17 | 32 | 15 | 9 | 17 | 13 | 24 | 20 | 17 | 20 | 8 | 22 | 11 | 25 | 9 | ||
| 9 | 10 | 36 | 40 | 16 | 31 | 21 | ||||||||||||||||
| 10 | 29 | 12 | 28 | 9 | 16 | 16 | 15 | 11 | 28 | 1 | 29 | 33 | 39 | 26 | 34 | 7 | 25 | 28 | 7 | 38 | ||
List | ||||||||||||||||||||||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| H8:AA10 | Expression | =RANK(H8,$H8:$AA8)+COUNTIFS($H8:H8,H8)-1<=8 | text | NO |
Upvote
0
Solution
