Need a zero value

H

heater

I need a zero value when J5 and L5 are empty (blank). I keep getting
#VALUE!. I've tried the following formulas:
=IF(J5>0,L5/J5,IF(J5<0,0))
=IF(J5>0,L5/J5,0)
=IF(J5=0,"",L5/J5)
All return #VALUE!
 
D

Dave Peterson

=if(count(j5,l5)=0,"",.....)

or maybe...
=if(count(j5,l5)<>2,"",.....)

if both must be filled in.

=count() counts numbers
=counta() counts anything
 
K

KL

Hi,

Do you really mean the cells [J5] and/or [L5] are empty or do they return a
zero-length string as a result of a similar formula, such as
=IF(condition,1,""). If the latter is true then try the following formula:

=IF(OR(J5="",L5=""),"",L5/J5)

Regards,
KL
 
D

David Hepner

Looking at this again, I would use OR instead of AND


IF(OR(J5="",L5=""),0,L5/J5)
 
H

heater

That works too!

KL said:
Hi,

Do you really mean the cells [J5] and/or [L5] are empty or do they return a
zero-length string as a result of a similar formula, such as
=IF(condition,1,""). If the latter is true then try the following formula:

=IF(OR(J5="",L5=""),"",L5/J5)

Regards,
KL


heater said:
I need a zero value when J5 and L5 are empty (blank). I keep getting
#VALUE!. I've tried the following formulas:
=IF(J5>0,L5/J5,IF(J5<0,0))
=IF(J5>0,L5/J5,0)
=IF(J5=0,"",L5/J5)
All return #VALUE!
Click to expand...
Click to expand...
 
Top