Handling Formula-Based Blanks

The N function to the rescue

It is common to display a blank cell using the IF function and “”. A problem can arise when you want to use that IF formula in a calculation. Here is an easy way to cope.

In the image below cell F2 has a VLOOKUP to extract the value.

This works while a valid entry is in cell D2.

If D2 has no entry the VLOOKUP returns an error – see below.

To handle a blank input cell we add an IF function to display a blank cell if D2 is blank, but that breaks the multiplication calculation in cell G2, see below.

We use two quotation marks together “” to identify and display a blank cell.

The fix for cell G2 is easy and short. The formula is


And we change it to


The N function changes all text to 0 so now the multiplication works. The IF function returns text when it uses “”.

A valid code works as usual.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.