Google Sheets Conditional Formatting to Highlight Minimum Value but Ignoring Blank Cells

Question

I'm trying to create a conditional formatting rule to highlight cells within a specific range with the lowest minimum value BUT using the rounded value (to two decimal places), NOT the true value.

Example:

Header A	Header B
$7.499	$7.5
$8	$7.5001
$7.555	$9

I've tried these conditional formatting rules...

=ROUND(A2:B=MIN($A$2:$B),2)

^This only returns A2 ($7.499) as TRUE but should return A2, B2, and B3 as TRUE.

=A2:B=ROUND(MIN($A$2:$B),2)

^This only returns values of $0 (zero) as TRUE. Maybe because it needs to ignore blank cells?

=A2:B=ROUND(MIN(FILTER($A$2:$B,$A$2:$B>0)),2)
=A2:B=ROUND(MIN(FILTER($A$2:$B,$A$2:$B<>0)),2)

^So I tried both of these variations with a filter in an attempt to ignore blank cells, but then it doesn't work at all.

These columns are formatted as currency but the true value behind what is "displayed" are the values I've shown in the example table.

If anyone could help, I would be very appreciative. Thank you!

Answer 1

You also need to rounding before calculate minimum value. Try-

=INDEX(ROUND(A2:B4,2)=MIN(ROUND(A2:B4,2)))

In conditional formatting rule-

=INDEX(ROUND(A2,2)=MIN(ROUND($A$2:$B$4,2)))

Answer 2

I came close many times but kept running into the obstacle of it treating blank cells as zeros. I could have had it ignore zeros but I didn't want it to ignore actual literal zeros. Instead, here is what I finally came up with in the end:

= AND(NOT(ISBLANK(A2)), ROUND(A2, 2) = MIN(ARRAYFORMULA(IF(NOT(ISBLANK($A$2:$B)), ROUND($A$2:$B, 2), ))))