#DIV/0! Error

Patrick · Jan 14th, 2002, 05:17 PM

Can anyone tell me why this insists on bringing up the #DIV/0! Error
=IF((E6-F6)/E6="0","",(E6-F6)/E6)

shalini · Apr 26th, 2002, 02:31 AM

"IF((E6-F6)/E6="0","",(E6-F6)/E6)"

most probably E6 has 0 or no value. Try checking the value of E6 instead of checking the whole formula in the if condition.

Aladin Akyurek · Apr 26th, 2002, 02:44 AM

In addition to what Shalini noted:

Any number including 0 divided by 0 is undefined and Excel renders this as #DIV/0!

An empty cell in math expressions interpreted by Excel as 0.

Rewrite your formula as:

=IF(E6,(E6-F6)/E6,"")

Chris Davison · Apr 26th, 2002, 01:26 PM

your first arguement that says if it's equal to "0" is telling it to recognise a textual value of "0"

even if your (E6-f6/e6) is zero, it's not recognising it as text, and thusly defaulting to your 2nd arguement, which results in a division by zero....

drop the "quotes" or go with Aladin's more compact solution

Aladin Akyurek · Apr 26th, 2002, 01:45 PM

Quote:

On 2002-04-26 12:26, Chris Davison wrote:
your first arguement that says if it's equal to "0" is telling it to recognise a textual value of "0"

even if your (E6-f6/e6) is zero, it's not recognising it as text, and thusly defaulting to your 2nd arguement, which results in a division by zero....

drop the "quotes" or go with Aladin's more compact solution

Guess what: Dropping the quotes won't help when E6=0 or empty

.

Chris Davison · Apr 26th, 2002, 06:57 PM

Guess what : I posted without testing it first.

Naughty Chris

Apr 26th, 2002, 01:26 PM	#4
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	your first arguement that says if it's equal to "0" is telling it to recognise a textual value of "0" even if your (E6-f6/e6) is zero, it's not recognising it as text, and thusly defaulting to your 2nd arguement, which results in a division by zero.... drop the "quotes" or go with Aladin's more compact solution __________________ :: Pharma Z - Family drugstore ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Jan 14th, 2002, 05:17 PM	#1
Patrick Board Regular Join Date: Mar 2002 Location: Canada Posts: 50	Can anyone tell me why this insists on bringing up the #DIV/0! Error =IF((E6-F6)/E6="0","",(E6-F6)/E6)

Apr 26th, 2002, 02:31 AM	#2
shalini New Member Join Date: Apr 2002 Posts: 4	"IF((E6-F6)/E6="0","",(E6-F6)/E6)" most probably E6 has 0 or no value. Try checking the value of E6 instead of checking the whole formula in the if condition.

Apr 26th, 2002, 02:44 AM	#3
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	In addition to what Shalini noted: Any number including 0 divided by 0 is undefined and Excel renders this as #DIV/0! An empty cell in math expressions interpreted by Excel as 0. Rewrite your formula as: =IF(E6,(E6-F6)/E6,"")

Apr 26th, 2002, 06:57 PM	#6
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Guess what : I posted without testing it first. Naughty Chris