• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lowes scores only color once

Belleke

Well-Known Member
In a file the lowest scores are colored with CF. You can select number of lowest scores in N2.
2 lowest scores, 3 lowest scores ......
How can I change that if there are double scores cells should only colored once.
for example in row 8 number 46 should only colored once.
You can use VBA.
Thanks in advance
 
Upload sample file please. Clearly demonstrating your expected result as well as current set up.
 
Hi ,

The following formula gives the correct results where there are duplicates.

Merely using COUNTIF ensures that duplicates are not coloured , but since they are still used to determine the smallest three numbers , the smallest three numbers do not really exclude duplicates.

=AND(OR(C3 = SMALL($C3:$L3,1), C3 = SMALL($C3:$L3,COUNTIF($C3:$L3,SMALL($C3:$L3,1)) + 1), C3 = SMALL($C3:$L3,COUNTIF($C3:$L3,SMALL($C3:$L3,1)) + COUNTIF($C3:$L3,SMALL($C3:$L3,2)) + 1)), COUNTIF($C3:C3,C3) = 1)

Narayan
 
Hi ,

Sorry , but the formula given hard-codes the number 3 for the smallest three numbers , and thus does not take the choice in $N$2 into account.

Will have to revise the formula to take this into account. But as long as the choice is restricted to 2 or 3 , this method can be used.

Narayan
 
It is for a club and they changed there minds again, sorry
When there is 3 in N2 and you have
38383800152
then the 2 zero's and 1 38 should be colored (the first 3 lowest scores.
Thanks
 
Back
Top