Dairy Science and Food Technology Homepage
Home  Cheese starters Donate support Site  Probiotics Cheese yield  Cheese quality Cheese science  Italian cheese Anti-microbial  proteins  Phages Primary production  Ice cream Wine Calculators  Harvard referencing  Packaging  Jobs
Forum Home Forum Home > Site calculators and spreadsheet queries > Spreadsheet and site calculators
  New Posts New Posts RSS Feed - #DIV/0! messages
  FAQ FAQ  Forum Search   Events   Register Register  Login Login


#DIV/0! messages

 Post Reply Post Reply
Author
Message Reverse Sort Order
formula123 View Drop Down
Admin Group
Admin Group

Administrator

Joined: 01 Sep 2009
Location: N. Ireland
Status: Offline
Points: 390
Post Options Post Options   Thanks (0) Thanks(0)   Quote formula123 Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
Sponsored Links


Back to Top
formula123 View Drop Down
Admin Group
Admin Group

Administrator

Joined: 01 Sep 2009
Location: N. Ireland
Status: Offline
Points: 390
Post Options Post Options   Thanks (0) Thanks(0)   Quote formula123 Quote  Post ReplyReply Direct Link To This Post 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!
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.