Forum Discussion
conditional formatting
I'm trying to highlight the lowest 10 scores out of 20 for my golf handicap. I used conditional formatting rule "Format only top or bottom values" and I used Bottom with the value 10 as the ranking value, then used red text as the format. When I click OK the bottom 10 scores are highlighted in red text but if highest value of the 10 is duplicated it gets counted and turns red so I end up with more than 10 values highlighted in red. Example: The there are 12 values out of the 20 that are 86 and lower and there are 3 scores that equal 86 excel is counting each score of 86 and highlighting all 12 instead of counting one score of 86 and highlighting 10. How do I not count the duplicates to highlight the lowest 10 scores.
6 Replies
Using the same logic as in previous post it could be done without helper columns applying the formula to the rule like
=D4*(1+COLUMN(D4)/10000000000)<=AGGREGATE(15,6,$D4:$W4*(1+COLUMN($D4:$W4)/10000000000),10)
I'm not sure what shall be the logic if blank cells, thus ignored that case.
Second sheet in attached file.
Thank all that replied, I received a method that I modified and it works here is the formula I used:
=Sheet1!I4+(COUNTIF(Sheet1!$D$4:Sheet1!I4,Sheet1!I4)-1)*0.1
I added "sheet1" to the work book and copied the values used in my master, the formula adds a fraction to like values and eliminates the highest fraction value thus leaving the lowest 10 values to accept the formatting.
Thank all of you that replied.
I think he deleted his account? Null Null?
Anyway, I've learnt someone new from that formula so thanks for that. That's very clever.
Philip, null null appears for the users who have no first/last name in AAD account, more about that is here https://techcommunity.microsoft.com/t5/Community-Ideas/null-null-Members/idi-p/175450
Thanks for the positive feedback
- Dear If possible, try to increase the decimal value of the numbers. In case there is a number 86 as a round off figure then you can increase the decimal places. By this some values, will become like 85.90, 85.88 etc. Hence excel will be able to decipher. In case, the no. is strictly 86 then it has to give equal respect to same value while applying sort ascending or descending, In such a case, conditional formatting will not be able to pick 10 counts rather will pick the condition and will give a count nearest to 10. In your case, that's 12 you said. So according to me it won't change automatically and may require manual intervention. Thanks.
Here is one way to do it.. i have created some helper rows that add some numbers behind the decimal and then using those helper cells to in the formula for the formating.
