Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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), "") |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
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), "") |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|