I'm trying to create conditional formatting for range K6:AF78 to highlight the lowest value(s) in each row green, and second lowest value blue. The problem I'm running into is when there are duplicate values. It either only highlights one of the values green, or it highlights both of the values blue, or also highlights cells with zero value. I am wanting it to highlight all of the lowest values even if there are multiple duplicates, and then also all of the second lowest values, again even if there are multiple duplicates, and ignore cells with zero value.

It also doesn't seem to want to apply the conditional formatting to the entire range properly either, so I have to create conditional formatting for each individual row which is a pain. It seems to only apply it to some rows, or highlights seemingly random values within each row.

I have tried the following formulas in conditional formatting so far with minimal success:

To find the lowest value: =IF(SMALL($K$6:$AF$78,1)=K6, TRUE, FALSE) I have also tried =K6=MIN($K6:$AF78)

To find the second lowest value: =IF(SMALL($K$6:$AF$78,2)=K6, TRUE, FALSE)

Here is a sample of the data I'm working with (the columns are shifted over compared to my references above, but the same info still applies):

I'm trying to create conditional formatting for range K6:AF78 to highlight the lowest value(s) in each row green, and second lowest value blue. The problem I'm running into is when there are duplicate values. It either only highlights one of the values green, or it highlights both of the values blue, or also highlights cells with zero value. I am wanting it to highlight all of the lowest values even if there are multiple duplicates, and then also all of the second lowest values, again even if there are multiple duplicates, and ignore cells with zero value.

It also doesn't seem to want to apply the conditional formatting to the entire range properly either, so I have to create conditional formatting for each individual row which is a pain. It seems to only apply it to some rows, or highlights seemingly random values within each row.

I have tried the following formulas in conditional formatting so far with minimal success:

To find the lowest value: =IF(SMALL($K$6:$AF$78,1)=K6, TRUE, FALSE) I have also tried =K6=MIN($K6:$AF78)

To find the second lowest value: =IF(SMALL($K$6:$AF$78,2)=K6, TRUE, FALSE)

Here is a sample of the data I'm working with (the columns are shifted over compared to my references above, but the same info still applies):

• excel
• formula
• conditional-formatting

• Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Bot Commented Mar 17 at 22:12

2 Answers 2

For the lowest non-zero value(s) of each row:

=K6=AGGREGATE(15,6,$K6:$AF6/($K6:$AF6<>0),1)

For the second lowest non-zero value(s) of each row:

=K6=AGGREGATE(15,6,$K6:$AF6/($K6:$AF6<>0),COUNTIF($K6:$AF6,AGGREGATE(15,6,$K6:$AF6/($K6:$AF6<>0),1))+1)

Post-comment edit

The previous formulas will work if the rows cointains just numbers. Assuming every input is formed by a dollar sign and the number, the following formulas should be applied.

For the lowest non-zero value(s) of each row:

=IF(K6="",FALSE,SUBSTITUTE(K6,"$","")*1=AGGREGATE(15,6,SUBSTITUTE($K6:$AF6,"$","")*1/(SUBSTITUTE($K6:$AF6,"$","")*1<>0),1))

For the second lowest non-zero value(s) of each row:

=IF(K6="",FALSE,SUBSTITUTE(K6,"$","")*1=AGGREGATE(15,6,SUBSTITUTE($K6:$AF6,"$","")*1/(SUBSTITUTE($K6:$AF6,"$","")*1<>0)/(SUBSTITUTE($K6:$AF6,"$","")*1<>AGGREGATE(15,6,SUBSTITUTE($K6:$AF6,"$","")*1/(SUBSTITUTE($K6:$AF6,"$","")*1<>0),1)),1))

These formulas will still work in case only numbers are specified or a mixed dataset of only numbers and numbers with dollar sign.

Side note: it might be advisable as practice to store the data just as numbers and then apply a currency format. To specify the currency and the numbers might be tedious and unpractical.

To find the lowest value you can use:

=AND(K6<>0, K6=MIN($K6:$O6))

Make sure to adjust your ranges as required. To find the second lowest you can use:

=AND(K6<>0, K6=SMALL(IF($K6:$O6<>MIN($K6:$O6), $K6:$O6), 1))

Edit, amended formulas to exclude zeros and blanks:

=AND(F1<>0, F1=MIN(FILTER($F1:$K1,($F1:$K1<>"")*($F1:$K1<>0))))
=AND(F1<>0, F1<>"", F1 = LARGE(IF(($F1:$K1<>0) * ($F1:$K1<>""), $F1:$K1), 2))