#DIV/0! messages

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

   There are lots of excellent Excel resources on the web and I will try to post some of these when I get time. However this message could easily be generated by users modifying my spreadsheets or any other spreadsheet and I will try to explain how to deal with it.

The message is generated when a number is divided by 0;  this can easily happen when doing calculations. In many cases the appearance of this error notice in a cell is no big deal but it does not look good.

How can you deal with it?

1) You can arrange for Excel to give a value of your choice e.g. 0.00 by using a conditional statement. In the cell, A3, below the #DIV/0 error has been generated because A1 has been divided by A2.

To avoid this error in Excel 2007 or 2010, the IF ERROR function can be used. The syntax is =IFERROR( value , value_if_error ).  If you wish to replace the #DIV message with say 0, then the formula at A3 should be replaced as indicated below:

A1/A2 is the calculation that we wish to perform and ,0 is the error message.

Instead of having 0 as the error message we could arrange for a text message to be shown. Do this by replacing the 0 with the message in quotations or inverted commas as indicated below: 

The IF ERROR function is not available in older versions of Excel and a standard IF function can be used instead. However the syntax is a little different.

Author	Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic
Admin Members Profile Send Private Message Find Members Posts Add to Buddy List Admin Group Administrator Joined: 01 Sep 2009 Location: N. Ireland Status: Offline Points: 390	Post Options Post Reply Quote Admin Report Post Thanks(0) Quote Reply Topic: #DIV/0! messages Posted: 18 May 2011 at 11:58pm
	There are lots of excellent Excel resources on the web and I will try to post some of these when I get time. However this message could easily be generated by users modifying my spreadsheets or any other spreadsheet and I will try to explain how to deal with it. The message is generated when a number is divided by 0; this can easily happen when doing calculations. In many cases the appearance of this error notice in a cell is no big deal but it does not look good. How can you deal with it? 1) You can arrange for Excel to give a value of your choice e.g. 0.00 by using a conditional statement. In the cell, A3, below the #DIV/0 error has been generated because A1 has been divided by A2. To avoid this error in Excel 2007 or 2010, the IF ERROR function can be used. The syntax is =IFERROR( value , value_if_error ). If you wish to replace the #DIV message with say 0, then the formula at A3 should be replaced as indicated below: A1/A2 is the calculation that we wish to perform and ,0 is the error message. Instead of having 0 as the error message we could arrange for a text message to be shown. Do this by replacing the 0 with the message in quotations or inverted commas as indicated below: The IF ERROR function is not available in older versions of Excel and a standard IF function can be used instead. However the syntax is a little different.

Sponsored Links

Admin Members Profile Send Private Message Find Members Posts Add to Buddy List Admin Group Administrator Joined: 01 Sep 2009 Location: N. Ireland Status: Offline Points: 390	Post Options Post Reply Quote Admin Report Post Thanks(0) Quote Reply Posted: 17 May 2011 at 10:22pm
	Dear PM? This is to acknowledge your PM re how to configure an output cell not to show #DIV/0! messages. I will respond shortly- I have no record of you downloading a spreadsheet!