• 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
 

Chihiro

Excel Ninja
Upload sample file please. Clearly demonstrating your expected result as well as current set up.
 

NARAYANK991

Excel Ninja
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
 

NARAYANK991

Excel Ninja
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
 

Belleke

Well-Known Member
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
 
Top