Suppressing Some Errors and Not Others

Jesus has formulas that sometimes return errors because source data hasn't been entered yet. He wonders how he can suppress only these expected errors without hiding other errors that might indicate a genuine problem with his formulas.

This question is a bit fuzzy, as it is unclear under what exact situations an error may occur. Jesus mentions source data not be entered, but it is unclear what that means. For instance, if the absence of a value in a single cell can cause an error, that is different from the absence of values in two, seven, or thirty-four cells.

Let's say, for instance, that cell B7 is used as the denominator in a formula elsewhere in your worksheet. If cell B7 is empty or contains 0, then the formula returns a #DIV/0! error. The way around this is to modify your formula so that it checks whether B7 is 0 or not, similar to this:

=IF(B7=0,"Enter value in B7",A2/B7)

This approach will work whether B7 is empty, it contains the value 0, or it contains a formula that returns a value of 0. This type of approach deals with the potential of a #DIV/0! error, but it won't affect other potential errors, like Jesus wants. Understand, however, that it may actually introduce additional errors if there are other formulas that rely on the results of this formula. That is because the IF formula can return either a text value ("Enter value in B7") or a numeric value (A2/B7). If the dependent formula expects a numeric result, then a text message can cause a cascading set of errors.

Even so, this is the general approach to use when dealing with missing source data in a worksheet: Evaluate the cells that are supposed to contain that source data and only take an action when the expected values are there. In any other instance, return a message that makes the user understand what is missing.

What if the value in B7 isn't being used as a denominator, but is instead the numerator or simply a component value in a larger formula? Well, in that case, a 0 value in B7 may be acceptable, but no value—a blank cell—is not acceptable. Nor would text be acceptable in the cell. In that case, the test would need to be different and more complex, as in the following:

=IF(OR(ISBLANK(B7),ISTEXT(B7)),"Enter value in B7",A2+B7)

Notice the use of the OR function which, in conjunction with ISBLANK and ISTEXT, determines if cell B7 is blank or contains text. If so, then the text message is returned, otherwise the addition is performed. The only time that an error would occur would be if cell B7 contained an error condition.

Note, as well, that the two situations discussed so far only evaluate what is in cell B7. In both instances, though, there are two cells that should rightfully be evaluated: A2 and B7 because both cells are used in the formula. This means that you may want your formula to evaluate what is in both of those cells before executing the formula. How you would approach that depends on exactly what you expect A2 (and B7) to contain, and then testing for it.

As you can probably imagine, the testing of input values for a formula can become quite complex. It involves figuring out what the valid input values are and then determining what should occur if those input values are missing or are outside of acceptable bounds. This has to be determined on a formula-by-formula basis and then the formula adjusted to handle those errant situations.

For this reason, if your formula uses a lot of input values or the evaluation of those input values can be complex, you may want to separate the evaluation from the final formula in some way. For instance, you might have your input values entered in cells B1:B22. Then, in cells C1:C47 you could have a series of formulas that evaluate those input values. Each formula could test for a specific acceptable or non-acceptable condition of the input values. The result of all those formulas could be ultimately distilled to a "Good" or "Bad" determination (or, perhaps 1 or 0 value) in cell C48. Every other formula in your worksheet could then be based on the result indicated in cell C48—if it is "Good" (or 1), then the formula proceeds as intended. If it is "Bad" (or 0), then nothing is returned by the formula and the user can refer to the test results in C1:C47 to figure out what the real problem is and fix it in the input values.

As you've probably figured out by now, there is no easy way to deal with input data, formulas, and errors. Every situation is different, and it takes some considered evaluation to plan how the three elements should relate to each other. Excel provides quite a few logical and error-related functions that you can use as you are constructing your evaluative formulas for your input values. How you put them together depends on you and your data.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (14009) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.