Google-sheets – Conditional Formatting: no color when cell is empty

conditional formattinggoogle sheets

I am using this custom formula for my sheet: =if(B2<((B$7/4)*$A2), 1, 0).

However, this also highlights the rest of the cells that are empty.

How do I exclude empty cells from this formula?

Best Answer

Use =and(len(B2), ... ) where ... is your current condition. The length value of 0 corresponds to False; any positive value is True.

A more explicit, but longer version is using not(isblank(B2)) instead of len(B2)

By the way, your construction = if( condition, 1, 0) is not necessary; simple = condition works. E.g., =B2<((B$7/4)*$A2) is a valid formula returning True or False according to the condition.

So, a complete formula would be =and(len(B2), B2<((B$7/4)*$A2)).

Related Topic