Having trouble viewing this email? Click here
March 16, 2017
Hi fellow Excel Addict,
Sorry this newsletter was supposed to go out yesterday (Thursday, March 16). I was in a big hurry to go out somewhere yesterday so I was rushing to send out my newsletter. This morning I finally got around to checking my email and discovered that I had forgotton to click SEND. Oops!!
If you missed my 'Excel in Seconds' newsletter on Tuesday, I showed how to 'Remove Workbooks From Your Recently-Used Workbooks List'. You can read it here.
In today's 'Excel in Minutes' tip, I'm going to show you how to 'Take Control of Those Annoying Formula Errors'. If you're still using the outdated ISERROR approach to handling formula errors, I'm going to show you something you've been missing out on — since 2007.
I hope you'll have a great weekend and keep on Excelling,
Keep on Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
Take Control of Those Annoying Formula Errors
If you've been using Excel for more than a few years then it is likely that more than once you have been frustrated when some of your formulas return error messages (e.g. #N/A, #NULL!, #NAME?, #NUM!, #DIV/0!, #REF! or #VALUE!).
You may have even looked for ways to prevent these errors from messing up your worksheets.
The traditional approach to handling formula errors
For many years, the traditional approach for handling these error messages was to use a combination of the ISERROR and IF functions.
The ISERROR function was used to determine if a formula (or a referenced cell) resulted in an error. It would return either TRUE or FALSE.
The IF function then took that TRUE or FALSE result from the ISERROR function and used one value (i.e. an empty string in this example) if TRUE or repeated the original formula if FALSE.
Excel users have complained for many years that this approach was too cumbersome and inefficient because it essentially required evaluating the same formula (e.g. B5/C5) twice.
The simple and efficient solution
With Excel 2007, the IFERROR function was added. This made the process of handling formula errors a whole lot easier and significantly more efficient.
With IFERROR you need to supply only two pieces of information. The value you want to evaluate for an error and the value you want returned in the case of an error.
=IFERROR (value, value_if_error)
If the value that is evaluated returns an error, the value_if_error value will be used, otherwise, value will be used.
Create your own custom error messages
Not only is this function simpler and removes the need for evaluating a formula twice, it also allows you to easily create your own custom error messages.
Not everything you read on the Internet is true
Even though it's been 10 years since IFERROR was introduced to Excel, an Internet search will still get you thousands of results suggesting the IF/ISERROR solution for getting rid of formula error messages. So if you're an old timer from pre-Excel 2007 days and are still using that old approach, give the IFERROR function a try and you'll see that it really is the best approach
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630