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?

1 Answer 1


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)).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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