CODEWITHSUNDEEP
google-sheets
MBAPres
MBAPres

Reputation: 1

How to eliminate highlighting duplicates in google sheets conditional formatting

I have a spreadsheet where I need to conditional format/highlight the lowest 3 scores in a row to reflect dropped scores that are part of a Total calculation. I'm using the SMALL function to successfully calculate the Total..=SUM(A2:I2)-SMALL(A2:I2,1)-SMALL(A2:I2,2)-SMALL(A2:I2,3) but when I try to use the SMALL function in the Custom Formula field of the Conditional Format it highlights 0,60,60,60 and not 0,60,60

119 101 60 100 0 109 60 60 112   TOTAL:601

If four of the values are 0, it will highlight all for 0's.. if 60 is the lowest score and there are 4 or more scores of 60, it will highlight all and not reflect that only 3 of the scores are actually dropped.

Is there another way (custom formula) that can only highlight the lowest 3 scores in the row even when the 3rd lowest may have duplicates in the row?

Upvotes: 0

Views: 174

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34370

I've come up with this formula (assuming values start in A1) which unfortunately is a bit long

=OR(A1<SMALL($A1:$I1,3),AND(A1=SMALL($A1:$I1,3),COUNTIF($A1:A1,SMALL($A1:$I1,3))<=(3-COUNTIF($A1:$I1,"<"&SMALL($A1:$I1,3)))))

or

=OR(A1<SMALL($A1:$I1,3),AND(A1=SMALL($A1:$I1,3),(COUNTIF($A1:A1,SMALL($A1:$I1,3))+COUNTIF($A1:$I1,"<"&SMALL($A1:$I1,3))<=3)))

The logic is that it highlights all cells which are less than the third smallest value, then any values (starting from the left) which are equal to the third smallest value until the total equals three.

I've changed the second row to show that it selects the second zero instead of the second 60.

enter image description here

Upvotes: 2

Related Questions