I WantTo Get Rid Of #Value
 

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)

I WantTo Get Rid Of #Value
 

try a simple one
=(L14<"")*(L140)*(L14-M14)
result is 0 when L14 is blank or is negative[less than 0]...literally
=IF(L14<=0,0,(L14-M14))..
--
*****
birds of the same feather flock together..



"Gatsby" wrote:

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)

I WantTo Get Rid Of #Value
 

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

"Gatsby" wrote in message
...
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)

I WantTo Get Rid Of #Value
 

Try this?

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

HTH
Cheers
Geoff

On Jan 25, 2:45 pm, Gatsby wrote:
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)

I WantTo Get Rid Of #Value
 

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

"Gatsby" wrote in message
...
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)

I WantTo Get Rid Of #Value
 

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

"Geoff Lilley" wrote in message
oups.com...
Try this?

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

On Jan 25, 2:45 pm, Gatsby wrote:
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)

I WantTo Get Rid Of #Value
 

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


"Gatsby" wrote:

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)

I WantTo Get Rid Of #Value
 

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

Gatsby (Not The Great)

"T. Valko" wrote:

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

"Gatsby" wrote in message
...
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)

I WantTo Get Rid Of #Value
 

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)

"David Biddulph" wrote:

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

"Gatsby" wrote in message
...
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)

I WantTo Get Rid Of #Value
 

Try this:

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

Biff

"Gatsby" wrote in message
...
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)

"David Biddulph" wrote:

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

"Gatsby" wrote in message
...
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)

I WantTo Get Rid Of #Value
 

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)

"T. Valko" wrote:

Try this:

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

Biff

"Gatsby" wrote in message
...
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)

"David Biddulph" wrote:

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

"Gatsby" wrote in message
...
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)

I WantTo Get Rid Of #Value
 

Sorry, Teethless Mama,
That doesn't work.

"Teethless mama" wrote:

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


"Gatsby" wrote:

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)

I WantTo Get Rid Of #Value
 

On Jan 27, 2:36 am, Gatsby wrote:
"T. Valko" wrote:
=IF(ISNUMBER(L14),MAX(0,L14-M14),"")

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), "")

I WantTo Get Rid Of #Value
 

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" wrote:

On Jan 27, 2:36 am, Gatsby wrote:
"T. Valko" wrote:
=IF(ISNUMBER(L14),MAX(0,L14-M14),"")

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), "")

I WantTo Get Rid Of #Value
 

On Jan 27, 7:21 am, Gatsby wrote:
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.