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
Printed Date: 22 Apr 2024 at 2:08am
Software Version: Web Wiz Forums 12.03 -

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!

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 -
Copyright ©2001-2019 Web Wiz Ltd. -