Donate SIGN UP

Excel Formula

Avatar Image
Chris100682 | 12:06 Fri 07th Mar 2008 | Technology
2 Answers
Hi,

I have the following formula

=IF(D32="",1,(D32/C6))

The fields D32 and C6 both equal zero. The value #DIV is displayed. How do I amend my query to stop this showing as #DIV but as 0

Many Thanks
Gravatar

Answers

1 to 2 of 2rss feed

Best Answer

No best answer has yet been selected by Chris100682. 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.
The #DIV/0! error value occurs when a formula divides by 0 (zero).

Cause
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.)
Cure
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.

It appears that you wish for any division by zero to equal zero. Although I cannot see the logic of this requirement (i.e. there is no context for the use of this formula), the result you want is given by

=IF(D32="",1,IF(C6=0,0,(D32/C6)))

1 to 2 of 2rss feed

Do you know the answer?

Excel Formula

Answer Question >>