Donate SIGN UP

Excel Forumla Help

Avatar Image
sunflower71 | 19:58 Thu 22nd Feb 2007 | Technology
7 Answers
When I do a spreadsheet which is formulated
I get #DIV/0! (as the cells do not include any data, I think)
How do I make this 0 ?
TIA if you can help
Gravatar

Answers

1 to 7 of 7rss feed

Best Answer

No best answer has yet been selected by sunflower71. Once a best answer has been selected, it will be shown here.

For more on marking an answer as the "Best Answer", please visit our FAQ.
I am no expert on Excel but #DIV/0 usually comes up when the digits are too many for the width of the column. Try widening the column and if this is the case, the numbers will appear.
Got this from typing #DVI/0 into excel help.
(double dutch to me !)


Using the cell reference to a blank cell or to a cell that contains zero as a divisor. (If an operand is a cell that is blank, Microsoft Excel interprets the blank as zero.) Change the cell reference, or enter a value other than zero in the cell used as a divisor. You can enter the value #N/A into the cell referenced as the divisor, which will change the result of the formula to #N/A from #DIV/0! to denote that the divisor value is not available.
To prevent an error value from displaying, use the IF worksheet function. If the cell used as the divisor is blank or contains a zero (0), then the IF function can display nothing instead of performing the calculation. For example, if cell B5 contains the divisor and A5 contains the dividend, use =IF(B5=0,"",A5/B5). The two quotation marks represent an empty text string.

Entering a formula that contains explicit division by zero (0) � for example, =5/0. Change the divisor to a number other than zero.
Running a macro that uses a function or a formula that returns #DIV/0!. Make sure the divisor in the function or formula is not zero or blank.



This shows you how to stop it appearing, http://support.microsoft.com/kb/182188
instead of just using

=(your formula)

use

=if(iserror(your formula),0,your formula)
Question Author
Thanks, thing is I am dividing �0 by 0.

when i do the suggestions I now get #NAME?

thanks alot though and i keep you informed!!!
well why are you trying to divide �0 by 0? the answer to that would be infinity so the computer sees it as an error your formula.. I can only presume your are going to be changing the field so that you're not actually dividing by 0? If that's the case, it'll all be ok as soon as you input the relevant data.
My suggestion should still work. It basically says if your formula gives an error, then show "0" otherwise show the answer to your formula.

for example,
=IF(ISERROR(A1/B1),0,A1/B1)

1 to 7 of 7rss feed

Do you know the answer?

Excel Forumla Help

Answer Question >>