Formula Help Needed

[puting_uwak]

Help please!

I have this formula in cell I31:

=IF(AND((OR(E31="Pipe",E31="Riser")),(NOT(H31=0))),"m",IF(AND((H31>1),(NOT(E31=""))),"nos.",IF(AND((H31=1),(NOT(E31=""))),"no.","")))

The problem is that I31 still returns "nos." if E31 has text and H31 is
blank. I31 should be blank if E31 contains text and H31 is either blank or
zero.

 

[Roger Govier]

Hi

I think you will find that H31 has a space character within it, and is not
blank.
Press Delete on cell H31 to ensure it is blank, and I think you will find
the formula works.

 

[David Biddulph]

You'll find it easier to analyse your formula if you delete the unnecessary
parentheses, and if you simplify by replacing NOT(H31=...) by H31<>...
Your
=IF(AND((OR(E31="Pipe",E31="Riser")),(NOT(H31=0))),"m",IF(AND((H31>1),(NOT(E31=""))),"nos.",IF(AND((H31=1),(NOT(E31=""))),"no.","")))
can be shortened to
=IF(AND(OR(E31="Pipe",E31="Riser"),H31<>0),"m",IF(AND(H31>1,E31<>""),"nos.",IF(AND(H31=1,E31<>""),"no.","")))

For either your formula or mine, if E31 has text and H31 is either blank or
zero, the result is blank, not "nos."
To get "nos." I guess that your H31 contains text, perhaps spaces?

 

[ytayta555]

On 11 Iun, 06:04, puting_uwak <[email protected]>
wrote:

This formula work for you ?

=IF(AND(ISTEXT((E31)),OR((H31)="",(H31)=0)),"","anYvalue")

 

[puting_uwak]

Thank you all so much for your replies!

ytayta555, sorry but your formula does not work for me. I need I31 to
return "no." if H31=1, "nos." if H31>1.

Roger and David, H31 actually contains a formula:
=IF(ISNUMBER(G31),G31*$C$3,"")

So I guess the space you are referring to is the "". Sorry for not
mentioning this, I thought this part wasn't the one screwing up. So how do I
make H31 return a blank cell either if G31 contains text or G31 is blank?

David, thanks for simplifying my formula. I'm new at this so I guess I must
be using the "long cut" method. Didn't know about the <> until you pointed
it out.

 

[ytayta555]

Roger and David, H31 actually contains a formula:
=IF(ISNUMBER(G31),G31*$C$3,"")
So how do I make H31 return a blank cell either if G31 contains text or G31 is blank?

=IF(OR(ISTEXT((G31)),COUNTBLANK(G31)=1),"",G31*$C$3)

Here is how must to look after the formula pattern you
have posted , but it still will be Not working :
=IF(AND(OR((E31)="Pipe",(E31)="Riser"),(H31)<>0),"m",IF(AND((H31)>1,
(H31)<>""),"nos.",IF(AND((H31)=1,(E31)<>""),"no.","")))

You have 3 IF's function in your formula .
We must work in 3 steps :

step 1) =IF(AND(OR((E31)="Pipe",(E31)="Riser"),(H31)<>0),"m",
step 2) IF(AND((H31)>1,(H31)<>""),"nos.",
step 3) IF(AND((H31)=1,(E31)<>""),"no.",""

In your 2 step , second IF is not logical , if H31 is >1
cann't be blank , so , step 2 must look :
IF(AND((H31)>1),"nos.", or , if you need to return "nos." either H1 is

1

or H1 is not blank you must use [maybe it was a typo..] :

IF(OR((H31)>1,(H31)<>""),"nos.",
so , your formula became :

1),"nos.",IF(AND((H31)=1,(E31)<>""),"no.","")))

or
=IF(AND(OR((E31)="Pipe",(E31)="Riser"),(H31)<>0),"m",IF(OR((H31)>1,
(H31)<>""),"nos.",IF(AND((H31)=1,(E31)<>""),"no.","")))

If there are still problems , is better to explain in steps

 

[ytayta555]

Typo from me ...

In your 2 step , second IF  is not logical , if H31 is >1
cann't be blank , so , step 2 must look :
IF(AND((H31)>1),"nos.",
IF((H31)>1,"nos.",

so , your formula became :

 

[puting_uwak]

ytayta555, wow, thanks so much for the thorough explanation. i agree, it's
easier to understand when the formula is outlined in steps. i tested out
your formula and it worked like mine just as you said. if it's okay, maybe i
can send you the excel file so you can see the problem better? i can send it
on monday, i'll be out this weekend so i cannot send it sooner. thanks again
for your reply. it really helped me understand composing formulas better.

 

[ytayta555]

ytayta555, wow, thanks so much for the thorough explanation.  i agree, it's

Of course you can , maybe with some more examples after/before ,
to my gmail or to (e-mail address removed)

 

[puting_uwak]

This is what I came up after having a long walk in the park last Sunday.

=IF(AND((OR(E4="Pipe",E4="Riser")),NOT(H4="")),"m",IF(AND(NOT(OR(E4="Pipe",E4="Riser",E4="")),H4=1),"no.",IF(AND(NOT(OR(E4="Pipe",E4="Riser",E4="")),NOT(H4=""),H4>1),"nos.","")))

I had to add NOT(H4="") in the third IF Formula for I4 to return a blank.
When I put NOT(H4=0) or (H4<>0), I4 returns "nos." I think it's because the
formula in H4 (i.e. =IF(ISNUMBER(G4),G4*$B$1,"") ) returns "" when it's
conditions are not met.

Thanks for all your help guys! Couldn't have thought of this without your
replies. Feel free to edit my formula, I might have put extra () where it
really isn't needed.

ytayta555, I still sent you the sample excel file in case you want to see
and improve on it. Thanks!!!

 

[David Biddulph]

You could probably shorten and simplify
=IF(AND((OR(E4="Pipe",E4="Riser")),NOT(H4="")),"m",IF(AND(NOT(OR(E4="Pipe",E4="Riser",E4="")),H4=1),"no.",IF(AND(NOT(OR(E4="Pipe",E4="Riser",E4="")),NOT(H4=""),H4>1),"nos.","")))
to
=IF(AND(OR(E4="Pipe",E4="Riser"),H4<>""),"m",IF(AND(E4<>"Pipe",E4<>"Riser",E4<>"",H4=1),"no.",IF(AND(E4<>"Pipe",E4<>"Riser",E4<>"",H4<>"",H4>1),"nos.","")))
and could shorten it further.

 

[Harlan Grove]

You could probably shorten and simplify ....
=IF(AND(OR(E4="Pipe",E4="Riser"),H4<>""),"m",
IF(AND(E4<>"Pipe",E4<>"Riser",E4<>"",H4=1),"no.",
IF(AND(E4<>"Pipe",E4<>"Riser",E4<>"",H4<>"",H4>1),"nos.","")))

and could shorten it further.

....

Indeed. There are many conditions that seem to head to "" return
value. It's arguably clearer to show all of them explicitly. The real
trick to simplification is handling E4 properly, which means testing
it as FEW times as possible.

=IF(E4="","",IF(OR(E4="Pipe",E4="Riser"),IF(H4<>"","m",""),
IF(H4=1,"no.",IF(N(H4)>1,"nos.",""))))