Excel: Allow dividing formulas to fail gracefully

I’m sure I’m not alone in having had this happen: I was using Excel to make some quick calculations, and got #DIV/0!-errors for some rows because one of the fields I’d been dividing by was empty. Sometimes you don’t have all the information you need for every single row, but you still want to have a standardised calculation to run where you do have the information you need.

Luckily, the IFERROR formula lets you define how to handle such errors. The syntax is as follows: IFERROR(value, value_if_error). The following example assumes you’re dividing the number in column A with the number in column B, and want Excel to return a dash if it encounters an error:

=IFERROR(A2/B2,"-")

This way, when it fails, it does so gracefully, and you can correct it – or ignore it – as best applies to your needs.

By posting a comment, you consent to our collecting the information you enter. See privacy policy for more information.

This site uses Akismet to reduce spam. Learn how your comment data is processed.