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)

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)

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)

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)

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)

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)

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)

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)

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

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

=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)

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)