Skip to content
Home » How to Ignore Formula / Not Calculate If Cell is Blank

How to Ignore Formula / Not Calculate If Cell is Blank

     

    In this video I demonstrate how to ignore a formula or stop calculating if a specified cell is blank or if two or more cells are blank. In our scenario we are calculating the percentage of calls (at a call centre) that are dropped. If either the total number of calls is blank or if the number of dropped calls is blank, we don’t want to perform the calculation.

    There are two methods that you can use to evaluate whether a cell is blank. The first method tests for an empty text string, eg =A1=””. This method will return TRUE if the cell is empty or if it contains an empty text string. The second method uses the ISBLANK function. This will return TRUE if the cell is empty but FALSE if it contains an empty text string. You will need to decide which method works best for your scenario, but the difference between the two tests is the focus of this tutorial.

    Click here to download the featured file.

    Empty Text String Method

    The Logical Test

    Click here to view this part of the video tutorial.

    To evaluate whether a cell contains an empty text string you would write the following logical test:

    =C2=””

    C2 being the cell you wish to evaluate. C2 in our example is the number of dropped calls.

    The IF Statement to Ignore Formula if Blank

    Click here to view this part of the video tutorial.

    =IF(C2=””,””,C2/B2)

    Cell B2 contains the total number of calls.

    ISBLANK Method

    The Logical Test

    Click here to view this part of the video tutorial.

    To evaluate whether a cell is empty using ISBLANK you would write a formula like this:

    =ISBLANK(C2)

    C2 being the cell you wish to evaluate. C2 in our example is the number of dropped calls.

    The IF Statement to Ignore the Formula if Blank

    Click here to view this part of the video tutorial.

    =IF(ISBLANK(C2),””,C2/B2)

    Cell B2 contains the total number of calls.

    Do the Two Methods Always Give the Same Result?

    Click here to view this part of the video tutorial.

    Initially, it appears as though the two methods give the same results and this is indeed true unless the cell you are evaluating for “blankness” contains a formula that has returned an empty text string.  If a cell contains an empty text string (normally, as the result of a formula) then ISBLANK will not see that cell as blank, so comparing the two methods (empty text string vs ISBLANK) will give different results.

    In summary:

    • Using the empty text string method will return TRUE (therefore ignore the formula) if the cell is empty or contains an empty text string.
    • Using the ISBLANK method will return TRUE (therefore ignore the formula) only if the cell is empty

    Testing Whether One or More Cells are Blank

    Click here to view this part of the video tutorial.

    To test whether one or more cells are blank use the OR function.  The OR function allows you to run multiple tests simultaneously and will return TRUE if at least one test is met.

    Example OR statement from the video:

    =OR(F2=””,B2=””)

    Example IF statement for ignoring formula if one or more cells is blank:

    =IF(OR(F2=””,B2=””),””,F2/B2)