Print Page | Close Window

#DIV/0! messages

Printed From: Dairy Science and Food Technology
Category: Site calculators and spreadsheet queries
Forum Name: Spreadsheet and site calculators
Forum Description: Support queries about calculators and spreadsheets downloaded from Dairy Science and Food Technology website or the ice cream mix subscription service
URL: https://www.dairyscience.info/forum/forum_posts.asp?TID=267
Printed Date: 29 Mar 2024 at 12:14pm
Software Version: Web Wiz Forums 12.03 - http://www.webwizforums.com


Topic: #DIV/0! messages
Posted By: formula123
Subject: #DIV/0! messages
Date 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!



Replies:
Posted By: formula123
Date 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.



Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.03 - http://www.webwizforums.com
Copyright ©2001-2019 Web Wiz Ltd. - https://www.webwiz.net