Highlight minimum value in each row, ignoring zeros and skipping columns

Question

I'm trying to highlight the smallest value in each row using conditional formatting (CF), while ignoring zeros and blanks. Also, I don't want the ranges to be continuous (i.e. skipping certain columns).

For example, give the following sheet:

   A    B    C     D      E
1 345   0   skip  123  <empty>
2  0   211  skip  234  <empty>

I would like to have D1 and C2 highlighted, independently.

I can get part way by using a custom formula in CF: =A1=min({$A1,$B1,$D1,$E1})

However I'm running into a couple of issues:

• Zeros and empty cells are highlighted (which I don't want)
• How to apply CF to each row independently without manually creating CF for each row (i.e. without having to do =A2=min({$A2,$B2,$D2,$E2}), =A3=min({$A3,$B3,$D3,$E3}), etc.)

Answer 1

I found another way to do it with a single CF formula using ArrayFormula:

=A1=ArrayFormula((MIN(IF({$A1,$B1,$D1,$E1}>0,{A1,$B1,$D1,$E1}))))

The key is to also Apply to Range: A1:A,B1:B,D1:D,E1:E

Answer 2

In your example, the entire range A2:D can be formatted in the specified way by using the custom formula

=and(column(A2) <> 3, A2 = min(filter({$A2,$B2,$D2}, {$A2,$B2,$D2} > 0)))

Which is a combination of two conditions:

• column(A2) <> 3 says to not format column C
• filter({$A2,$B2,$D2}, {$A2,$B2,$D2} > 0) says to ignore the empty cells and zeros when finding the minimum. The value of current cell being equal to the minimum of these (A2 = min(...)) ensures in particular that it's not zero or blank itself.