Need a zero value

[Guest]

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!

 

[Guest]

See if this works for you:

=IF(AND(J5="",L5=""),0,L5/J5)

 

[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

 

[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

 

[Guest]

It worked - Thanks!

See if this works for you:

=IF(AND(J5="",L5=""),0,L5/J5)

 

[Guest]

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


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

 

[Guest]

That works too!

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

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!