Google-sheets – Google Sheet formula on finding smallest value larger than 0 does not work inside conditional formatting

conditional formattinggoogle sheetsworksheet-function

I wanted to find and highlight the smallest value larger than 0, but the formula I am using does not work in the conditional formatting.

 =MIN(FILTER(L4:L100, (L4:L100)>0))

Namely, if I use it inside the sheet, it will find value 0.44 and write it out.

However, if I used it in the conditional formatting, it will mark ALL values larger than 0.

Anyone knows why the formula does not work in the conditional formatting?

Best Answer

I found a solution out of sudden. I have forgotten (did knot know it was needed) to add another condition.

The correct formula is:

 =L4:L100=MIN(FILTER(L4:L100, (L4:L100)>0),1)

Related Solutions

Google-sheets – Set conditional formatting on a column that depends on another cell’s text (not value)

Since you use the date value, we have a nice weekday() function we can make use of.

weekday(*yourDate*,2) will give you an integer from 1-7 counting from Monday. On the conditional formatting applied to your targetted range, we can then use the following custom formula to highlight weekends:

=weekday(B$1,2)>5

Google-sheets – Adapting Conditional Formatting Formula for Cross Sheet Reference

conditional formatting can't intake a cross-sheet reference without INDIRECT therefore try:
```
=COUNTIF(INDIRECT("Sheet1!A1:A"), A1)=1
```

and from there you can build an array:

=COUNTIF({INDIRECT("Sheet1!A1:A");
          INDIRECT("Sheet2!A1:A");
          INDIRECT("Sheet3!A1:A")}, A1)=1

Best Answer

Related Solutions

Google-sheets – Set conditional formatting on a column that depends on another cell’s text (not value)

Google-sheets – Adapting Conditional Formatting Formula for Cross Sheet Reference

Related Topic