If you are an experienced Excel consultant you know what this free Excel help article covers. If you are a small business owner looking for help on this topic, it means that you might have not come across this before. So exactly what is the problem and what does this article cover? A cell in your Excel spreadsheet could have the value “ERROR” OR “VALUE” inside it. This basically means there is an error with the contents of that cell. You might have input a wrong formula into cell or you might have erroneously used an Excel function in which case Excel will also prompt you an error message to correct the error. Excel has another elegant method to capture and process this error and that is using the ISERROR function for Excel 2007, 2008, 2010, and 2011. We’ll discuss the ISERROR function in this article.
One of the most common mathematical situations that generate an error is the “divide-by-zero”. Any number divided by zero actually gives the answer infinity. However this value is impractical in our computations. In a cell, on an Excel spread enter a formula such as -100/0 and see for yourself what the result is. You’ll get a result such as #DIV/0! Now if you have a lot of mathematical values and computations in your spreadsheet and if you think that there is a high probability of chances for the divide by zero situations to occur then it will only be prudent to capture that error and provide the user with a warning that there is an error.
Here is how we go about using the ISERROR function and how we can provide a warning to the user. Now suppose we have a record (row of fields) where we are calculating daily wages of employees. Let us assume we anticipate a divide-by-zero scenario in the cell J18. To find out if there is an error simple use the ISERROR function in cell K18 or an appropriate cell (preferably in the last cell of the record) as follows. = ISERROR(J18) . The ISERROR function returns two values, “TRUE” if there is an error and “FALSE” if there is no error. Let us introduce another function that we will be using in conjunction with ISERROR and this is the IF function.
Let us say the cell where we want to display the error is M18. All we need to input into this cell is.
=IF(ISERROR(J18), “Invalid Data in Record”). Notice how both the functions have been used.
Note that both these functions can also be input into the cell using menus and dialogs from the ribbon menu. For instance, to insert the ISERROR function, under the Formulas tab, click the Function Insert button (on the extreme left, marked with fx) on the ribbon. The Insert Function dialog opens. From the box of functions (titled Select a function), browse for the ISERROR function and select it. Click OK. Note that if you do not see the function in the list make sure “All” is selected in the drop-down list above. Another dialog is opened for you to enter the function’s arguments. Here click the cell that you wish to check for the error and it is automatically inserted into the Value field in the dialog. Click OK and the ISERROR function is inserted into the cell.
Similarly you can use the menu to insert the IF function too. In the case of the IF function the second dialog prompts for three arguments, one for the logical test, one for the value if this logical test function is true and the third if the result of the logical test function is false. So in our example the value of the cell J18 will go into the first field and the result “Invalid Data in Record” will go into the second field i.e. the result of if the logical test is true. The third argument can be left empty. Click OK and you are all set.