{"id":707,"date":"2019-02-28T10:54:46","date_gmt":"2019-02-28T16:54:46","guid":{"rendered":"http:\/\/www.iqaccountingsolutions.com\/blog\/?p=707"},"modified":"2019-03-22T22:09:52","modified_gmt":"2019-03-23T03:09:52","slug":"hide-divide-by-zero-errors-in-excel-using-if","status":"publish","type":"post","link":"https:\/\/www.iqaccountingsolutions.com\/blog\/hide-divide-by-zero-errors-in-excel-using-if\/","title":{"rendered":"Hide Divide By Zero Errors in Excel Using IF"},"content":{"rendered":"

You should all remember from math class that you can’t divide a number by zero. When you try to do it in Excel, the result of your formula will be #DIV\/0!<\/em>. In some cases this is inevitable. For example if your spreadsheet calculates percentage change in annual sales of inventory items, new items will produce a #DIV\/0!<\/em> error because prior year sales are zero.<\/p>\n

 <\/p>\n\n\n\n\n\n\n\n
<\/td>\n\n

A<\/p>\n<\/td>\n

\n

B<\/p>\n<\/td>\n

\n

C<\/p>\n<\/td>\n

\n

D<\/p>\n<\/td>\n

\n

E<\/p>\n<\/td>\n

Column E Formula<\/td>\n<\/tr>\n
\n

1<\/p>\n<\/td>\n

<\/td>\n\n

Year 1 Sales<\/p>\n<\/td>\n

\n

Year 2 Sales<\/p>\n<\/td>\n

\n

$ Change<\/p>\n<\/td>\n

\n

% Change<\/p>\n<\/td>\n

<\/td>\n<\/tr>\n
\n

2<\/p>\n<\/td>\n

Item 1<\/td>\n10,000<\/td>\n11,000<\/td>\n1,000<\/td>\n10%<\/td>\n=D2\/B2<\/td>\n<\/tr>\n
\n

3<\/p>\n<\/td>\n

Item 2<\/td>\n0<\/td>\n7,000<\/td>\n7,000<\/td>\n#DIV\/0!<\/td>\n=D3\/B3<\/td>\n<\/tr>\n
\n

4<\/p>\n<\/td>\n

Item 3<\/td>\n15,000<\/td>\n12,000<\/td>\n(3,000)<\/td>\n-20%<\/td>\n=D4\/B4<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

As expected, the % Change for Item 2 shows a divide by zero error because it had no sales in year 1. Excel does not have an option to suppress divide by zero errors, but it’s easily by done using the IF<\/em> function. If you haven’t used IF<\/em> before, it may help to read my previous tip<\/a> on that subject. The formula below tells Excel, if the prior year sales (for Item 2 that’s cell B3) is zero, then display the text between the quotation marks (in this case nothing), if not, divide the $ Change by Year 1 Sales.<\/em> The result is that the % Change appears blank for item 2 but for items 1 and 3 it looks the same as with the original formula.<\/p>\n

 <\/p>\n\n\n\n\n\n\n\n
<\/td>\n\n

A<\/p>\n<\/td>\n

\n

B<\/p>\n<\/td>\n

\n

C<\/p>\n<\/td>\n

\n

D<\/p>\n<\/td>\n

\n

E<\/p>\n<\/td>\n

Column E Formula<\/td>\n<\/tr>\n
\n

1<\/p>\n<\/td>\n

<\/td>\n\n

Year 1 Sales<\/p>\n<\/td>\n

\n

Year 2 Sales<\/p>\n<\/td>\n

\n

$ Change<\/p>\n<\/td>\n

\n

% Change<\/p>\n<\/td>\n

<\/td>\n<\/tr>\n
\n

2<\/p>\n<\/td>\n

Item 1<\/td>\n10,000<\/td>\n11,000<\/td>\n1,000<\/td>\n10%<\/td>\n=IF(B2=0,””,D2\/B2)<\/td>\n<\/tr>\n
\n

3<\/p>\n<\/td>\n

Item 2<\/td>\n0<\/td>\n7,000<\/td>\n7,000<\/td>\n<\/td>\n=IF(B3=0,””,D3\/B3)<\/td>\n<\/tr>\n
\n

4<\/p>\n<\/td>\n

Item 3<\/td>\n15,000<\/td>\n12,000<\/td>\n(3,000)<\/td>\n-20%<\/td>\n=IF(B4=0,””,D4\/B4)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

I made the % Change blank if Year 1 Sales = 0, but you could show whatever you want.
\nIf you want to display text when Year 1 Sales = 0 type whatever you want between the quotation marks. In this example you might use =IF(B3=0,”New”,D3\/B3)<\/em> to make the word New<\/em> appear as the % Change for any item with no Year 1 Sales.
\nIf you want to display a number, leave out the quotation marks and put the number after the =, as in =IF(B3=0,0,D3\/B3) to have a zero displayed when Year 1 Sales=0.
\nTo display the contents of another cell when Year 1 Sales are 0, enter the cell reference after the = in your formula, as in =IF(B3=0,G10,D3\/B3)<\/em>. Don’t use quotation marks or Excel will display the cell reference (G10) instead of the contents of that cell.<\/p>\n","protected":false},"excerpt":{"rendered":"

Nobody likes to see #DIV\/0!, Excel’s divide by zero error, on their spreadsheet. But there is no option to suppress it and sometimes your data makes the error inevitable. Fortunately you can use the IF function to hide or replace the divide by zero error message.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[9,5],"tags":[],"_links":{"self":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/707"}],"collection":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/comments?post=707"}],"version-history":[{"count":4,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/707\/revisions"}],"predecessor-version":[{"id":2404,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/707\/revisions\/2404"}],"wp:attachment":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/media?parent=707"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/categories?post=707"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/tags?post=707"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}