I WantTo Get Rid Of #Value

[Gatsby]

I want Cell N14 to complete ONLY when the entry is made in Cell L14.
Otherwise I want it blank. If I don't have anything in Cell L14, I'm getting
#VALUE, in N14. This continues all the way down to Column N60, until I make
an entry in the 'L's'.

This is the formula I'm using which is obviously wrong:
=IF(L14<=0,0,(L14-M14))
Help please?

Gatsby (Not The Great)

 

[driller]

try a simple one

=IF(L14<=0,0,(L14-M14))..

 

[T. Valko]

Do you have a formula in L14 that might be returning a blank ("")?

Try this:

=IF(ISNUMBER(L14),IF(L14<=0,0,L14-M14),"")

Biff

 

[Geoff Lilley]

Try this?

=IF(ISBLANK(L14),"",L14-M14)

HTH
Cheers
Geoff

 

[David Biddulph]

If you're getting #VALUE in N14 you've probably got some text in L14,
perhaps just a space.
Try =IF(ISNUMBER(L14),L14-M14,"")

 

[David Biddulph]

That will still give #VALUE if L14 has text, such as a space.

 

[Teethless mama]

=IF(OR(L14<=0,L14=""),0,L14-M14)

 

[Gatsby]

Yes. The formula in L14 is: =IF(H14="","",H14*0.2)

Gatsby (Not The Great)

 

[Gatsby]

David, you're right .I have text in L14: =IF(H14="","",H14*0.2)
But your solution is working for me, thanks. However N14 cannot have a value
less than zero and this will arise if L14 is less than M14. What do I do to
return a '0' in N14 should this arise?
Thanks in advance,

Gatsby (not The Great)

 

[T. Valko]

Try this:

=IF(ISNUMBER(L14),MAX(0,L14-M14),"")

Biff

 

[Gatsby]

I tried to copy and paste the formula into a cell but it moves rows and shows
the formula vertically down the column.

Gatsby (Not ThE Great)

 

[Gatsby]

Sorry, Teethless Mama,
That doesn't work.

=IF(OR(L14<=0,L14=""),0,L14-M14)

 

[joeu2004]

I tried to copy and paste the formula into a cell but it moves rows and shows
the formula vertically down the column.

Sounds like you forgot to include the leading "=" (equal sign). So
the formula is being entered as text, and the column is not wide
enough to accomodate the length of the text. Never mind if you do not
understand the explanation. Just be sure to insert "=" before
"IF(...)".

By the way, I wonder if you also need to validate M14. You might
need:

=if(and(isnumber(L14),isnumber(M14)), max(0, L14-M14), "")

 

[Gatsby]

Thank you very much for your reply.
Yes. Your formula works! Thanks again. And you're right- I don't understand
what your formula means.

Gatsby (Not The Great)

 

[joeu2004]

And you're right- I don't understand what your formula means.

Did you want an explanation? If so, do you mean that you do not
understand my explanation (guess!) about formula having been entered
as text; or do you mean you do not understand the embellished formula
of the form if(and(...)...)?

At the risk of being presumptuous and overwhelming, I will attempt to
clarify both.

First, if you type A1+A2 (literally) into a cell, it will be treated
as text, just as if you had typed Total. Excel does not attempt to
interpret it, even though it looks like a "formula" to the human eye.
If you type =A1+A2 (note the addition of "="), it is treated as a
formula, and Excel performs the computation. I had conjectured that
when you copy-and-pasted someone's formula (which seems correct as
written), the leading "=" was missing one way or another. So
if(isnumber(...)...) was treated as text, not as a formula.

As for my embellishment, namely:

=if(and(isnumber(L14),isnumber(M14)), max(0, L14-M14), "")

it says: compute the max only if __both__ L14 and M14 contain numeric
values (either constants or the result of a formula); otherwise,
display a "blank" cell. The function AND() is true only when __all__
of its parameters are true.

HTH.