Microsoft Excel has many functions. One of them is called the ISERROR function. With ISERROR you can return a true or false value if there is an error or not. Here is how to use the ISERROR function in Excel 2010.
ISERROR Function Syntax
Before we show an example of how the ISERROR function works lets take a look at the syntax. Here is what the syntax looks like:
In the spot that says value you want to but the data you want to test. For example if you have any of these errors, #N/A, #VALUE, #REF!, #DIV/0! , #NUM!, #NAME?, OR #NULL the function will return TRUE. If your value is not one of the errors above it will be FALSE.
ISERROR Example in Excel 2010
Now that we have the syntax and a basic idea of how the ISERROR function works lets look at an example. Below is a spreadsheet with simple formulas that calculate out to an error or an actual value.
As you can see the function calculates out to True for cells A1, A2 and A3. While cell A4 is False since it is not an error. This gives you a basic idea of how the ISERROR function works but there are other ways. One of the most common ways I have seen this function used is with the IF function. So to set this up we would need change our spreadsheet a little bit. So below I have column C with the Bills and their cost. Next to it followed by how many months I have to pay those bills. And finally in the E column the cost of those bills per month. For column E I’m dividing the bills total by the months to get the cost per month. As you can see below I get an error in cell E3.
The error in cell E3 also messes up my sum in cell E5. To fix this we can us the ISERROR function and the IF function to check if their is an error and replace it with a zero. Here is the syntax we will use in column E instead of cells C divide by cells D:
The formula above will check to see if the calculations results in an error. If it does the value for the cell will be 0. If it is false it will then divide the to columns together. Here is what it looks like in Excel.
As you can tell, by changing the formula to check for an error it also fixed my sum problem in E5.
- Excel Column Function Tutorial
- Use the Month Function in Microsoft Access
- Calculate Dates in Excel 2007 or 2010 Workbooks the Easy Way
- Working with VBA Loops in Microsoft Excel
- Calculate Remaining Days in the Year in Excel 2010