logo
  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Using IFERROR to capture Formula Errors in Excel

June 28, 2013 0 comments Article Easy, Excel

Here is another useful Excel Function that was introduced with Excel 2007 that just, well, ‘makes sense’

If you’re designing spreadsheets with lots of formulas and lots of data, there will come a time where you need to error-trap some values, so as not to show #DIV/0 or #N/A for example.

Whilst there are better ways, without a doubt, what I will frequently see is users using someone like this…

The problem here being: Your formulas might itself be very complex – and this method means you’re writing it twice, making your file twice as hard to maintain, change, fix issues etc. It’s also very system intensive, as you’re calculating the formulas twice every time.

Even in 2003, there were ways to improve on this, by being more selective in how you test for an error – but 2007 bring a much better way of doing it, in the form of IFERROR

The syntax is very simple:

And in a practical example (but a much simpler formula than you might typically have)

So you can see the result is the same as using IF(ISERROR()) approach, but the result is a cleaner, easier to maintain, formula.

Related

Tags: Excel, IFERROR

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Format or Remove Borders from a Slicer or Timeline in Excel
Populate a table by looping through items in a list / another table
Showing Data Bars as a 'Proportion to Total' in Excel
Creating Virtual Tables in Power BI Using DAX
Using SUMPRODUCT to create a Conditional Weighted Average in Excel
Showing a Picture Based on the Value of another Cell in Excel

Meta

  • Register
  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

Categories

Pages

  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Copyright System Secrets 2022 - Theme by ThemeinProgress