LEN returning strange result

[Rob]

Hi,

I getting an unexpected result when using the following formula, I'm
receiving #VALUE when the formula in J5 returns nothing (the formula in J5
uses "" double quotes to stop an error being shown).

=IF(LEN(J5>0),J5/2*145,"")

What formula would return nothing i.e. the "" double quotes would come into
play.

Thanks, Rob

 

[Dave Peterson]

Watch your parentheses:

=IF(LEN(J5)>0,J5/2*145,"")

I'd do this, too, but it doesn't matter to excel:
=IF(LEN(J5)>0,(J5/2)*145,"")

I just find it less confusing.

 

[Rob]

Thanks dave, works a treat.

Rob

Watch your parentheses:

=IF(LEN(J5)>0,J5/2*145,"")

I'd do this, too, but it doesn't matter to excel:
=IF(LEN(J5)>0,(J5/2)*145,"")

I just find it less confusing.

 

[Ron Rosenfeld]

Hi,

I getting an unexpected result when using the following formula, I'm
receiving #VALUE when the formula in J5 returns nothing (the formula in J5
uses "" double quotes to stop an error being shown).

=IF(LEN(J5>0),J5/2*145,"")

What formula would return nothing i.e. the "" double quotes would come into
play.

Thanks, Rob

Your first clause, LEN(J5>0) will always return either a 4 or a 5; so the
condition_if_true clause will always get executed, regardless of the content of
J5.

When J5 contains a non-numeric entry, the mathematical equation will give a
#VALUE! error.

To test for the length of the contents of J5, you should use:

LEN(J5)>0

So your formula could be:

=IF(LEN(J5)>0,J5/2*145,"")


--ron