Highlight lowest 5 out of 10 scores in a row without duplicates

I have spent parts of 4 days looking through threads that pertain to what I would like to do but to no avail. It seems simple enough and I have tried several formulas that I have found but none have worked. I would like to highlight the lowest 5 scores out of 10 in a row without duplicates. It is the last part of a spreadsheet to make it easier for me to see which scores are being used for a handicap calculation. Any help at all would be nice. Thank you.

John

Hello,

Try the following:

1) Highlight the row/cells you wish to check for lowest scores
2) Click on "Conditional Formatting"
3) Click on "Highlight Cells Rules"
4) Click on "More Rules..."
5) Under "Select a Rule Type" click on "Format only top or bottom ranked values"
6) Change the settings, from left to right, as follows:
6a) "Bottom"
6b) 5
6c) Unchecked
7) Under "Preview" click on the "Format" button
8) Change the format to whatever you desire (e.g. bold red text, bright yellow fill, etc.)
9) Click on "OK"
10) Click on "OK"

Hopefully Excel 2007 is similar enough to 2010 that this helps. Let me know if you have any questions, and don't forget to star whoever helps you figure out your question!


Regards,
Xervice

Thank you for getting back to me. Unfortunately what you had me try did not work for what I need. I would like it to highlight lowest 5 scores out of 10 with no duplicates. It highlighted a sixth cell as it had the same score as the fifth cell.

I've attached a simple workbook so you can see what I'm trying to do.

Try this:

Conditional Formatting:

=AND(C2<=SMALL(IF(TRANSPOSE(FREQUENCY($C2:$L2,$C2:$L2))>0,$C2:$L2),5),COUNTIF($C2:C2,C2)=1)

Sorry this formula didn't work either. Actually nothing happens when I typed it in. I made sure I typed it in exactly how it shows in the thread.

Originally Posted by Jjoseph6969

Sorry this formula didn't work either. Actually nothing happens when I typed it in. I made sure I typed it in exactly how it shows in the thread.

It works on my computer XL-2007. Don't type. Select your range then copy and paste into CF

Thanks daddylonglegs, the first formula works great. Thank you to everyone else who helped also.

John