The Excel Addict - Help with Excel 2013, 2010,

Having trouble viewing this email? Click here

March 16, 2017
 
Greetings from The Excel Addict

Hi fellow Excel Addict,

Francis Hayes (TheExcelAddict.com)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)
Email:  fhayes[AT]TheExcelAddict.com



 


If you missed my last newsletter, you can click here to view it online.
 

 
Quote of the Day

"What we can do or cannot do; what we consider possible or
impossible is rarely a function of our true capability. It is more
likely a function of our beliefs about who we are."

-- Anthony Robbins --
 
If you have a favourite quote, send it to me and I may post it in my newsletter.

Excel in Minutes with TheExcelAddict.com

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!).

Formula Error in Microsoft Excel 2007 2010 2013 2016 365
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.

Iserror Function Blank in Microsoft Excel 2007 2010 2013 2016 365 

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.

=IF(ISERROR(B5/C5),"",B5/C5)

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.

=IF(TRUE,"",B5/C5)

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.

IFERROR Function in Microsoft Excel 2007 2010 2013 2016 365

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.

Create A Custom Formula Error Message in Microsoft Excel 2007 2010 2013 2016 365

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





If you've found this tip helpful, please share it.





Thanks for supporting this newsletter and website



Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"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