Changing this formula?

  • Thread starter Thread starter StargateFanFromWork
  • Start date Start date
S

StargateFanFromWork

=IF(L2<>"",IF(M2=0,"OKAY!",IF(M2<>0,"RE-VERITY (or M1 needs a value in
it!)!!!","")),"")


I'm stuck and am probably going 'round in loops, but I need to add one more
condition. If L2 is blank, there needs to be no text.

The other 2 conditions, if M2 = 0 and if M2 is not 0, work just fine, it's
just L2 that needs fixing.

Can someone help pls?

Thank you!! :oD
 
One way:

=IF(L2="","",IF(M2=0,"OKAY","RE-VERIFY (or M1 needs a value in
it)!!!"))

Note that you don't need your IF(M2<>0...) condition, since the
"RE-VERIFY..." part will only be executed if M2=0 is False.

NOTE: Your formula should have already given you a blank if L2 was
blank. Perhaps you're using the spacebar to "clear" L2, inserting a text
space character (so L2 is therefore NOT blank). If that's the case,
perhaps:

=IF(LEN(TRIM(L2))=0,"",IF(M2=0,"OKAY","RE-VERIFY (or M1 needs a
value in it)!!!"))
 
StargateFan,

It appears that if L2 is empty, you'll indeed get no text (""). Your formula can be
reduced, for the case of L2 being empty, to:

=IF(L2<>"", (snip) ,"")

Ensure that L2 is truly empty. You can examine its contents (or formula results) with
=LEN(L2), which should be 0.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
Earl Kiosterud said:
StargateFan,

It appears that if L2 is empty, you'll indeed get no text (""). Your
formula can be reduced, for the case of L2 being empty, to:
Click to expand...

That's what is confusing. L2 is not exactly empty at present, it has a
formula in it, perhaps that's why I get the text "OKAY!". I couldn't figure
that out but your question made me look to see what was in L2.

So that isn't going to work. And it's what I usu. do when I need a cell to
be empty until another cell has something in it. Hmmm ... I'll need to
think on this some more. I believe I just have too much going on. Will
report back.
 
JE McGimpsey said:
One way:

=IF(L2="","",IF(M2=0,"OKAY","RE-VERIFY (or M1 needs a value in
it)!!!"))

Note that you don't need your IF(M2<>0...) condition, since the
"RE-VERIFY..." part will only be executed if M2=0 is False.
Click to expand...

Ah, gotcha. I'm stitching together code from here and there and that's what
I get ... said:
NOTE: Your formula should have already given you a blank if L2 was
blank. Perhaps you're using the spacebar to "clear" L2, inserting a text
space character (so L2 is therefore NOT blank). If that's the case,
perhaps:

=IF(LEN(TRIM(L2))=0,"",IF(M2=0,"OKAY","RE-VERIFY (or M1 needs a
value in it)!!!"))
Click to expand...

I see what the problem is. L2 has a formula in it, that's why I'm not
getting a blank cell and get OKAY in it whether or not L2 has a number in it
or not. Is there a workaround for that??

Thanks! :oD
 
It would be helpful if you'd post at least the result of the formula in
L2 - it's hard to guess what solution to propose.
 
JE McGimpsey said:
It would be helpful if you'd post at least the result of the formula in
L2 - it's hard to guess what solution to propose.
Click to expand...

Oh, I'm sorry. As always, it's hard to write out what a situation is,
sometimes. Okay, dummy formula person here ... I re-wrote the formula for
cell N2, and the result finally is closer to what I need. Sorry for the
confusion. I realize that somewhere along the way the first part of the
formula which should have had <>, got an equal sign in it.

=IF(K3<>"",IF(M2=0,"OKAY!","RE-VERIFY (or M1 needs a value in it)!!!"))

Now the only difficulty lies in the fact that I'm getting a FALSE in N2 when
K3 is empty. Perhaps if I get that part fixed, I can figure out where to go
from there. So, how _does_ one get rid of a FALSE condition easily?

<phew> This one was a toughie, don't know why.

Thanks. :oD
 
Try this:

=IF(K3<>"",IF(M2=0,"OKAY!","RE-VERIFY (or M1 needs a value in
it)!!!"),"")

which is what Earl has already suggested.

Hope this helps.

Pete
 
One way:

=IF(K3="", "", IF(M2=0,"OKAY!","RE-VERIFY (or M1 needs a value in
it)!!!"))

which is essentially what I recommended before...

If you're
 
Back
Top