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?

migrated from superuser.com Feb 16 '18 at 3:47

This question came from our site for computer enthusiasts and power users.


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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.