Formula Help Needed
 

Help please!

I have this formula in cell I31:

=IF(AND((OR(E31="Pipe",E31="Riser")),(NOT(H31=0))) ,"m",IF(AND((H311),(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.

Formula Help Needed
 

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.

--
Regards
Roger Govier

"puting_uwak" wrote in message
...
Help please!

I have this formula in cell I31:

=IF(AND((OR(E31="Pipe",E31="Riser")),(NOT(H31=0))) ,"m",IF(AND((H311),(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.

Formula Help Needed
 

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((H311),(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(H311,E31<""),"nos.",IF(AND(H31=1,E31<""),"n o.","")))

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?
--
David Biddulph

"puting_uwak" wrote in message
...
Help please!

I have this formula in cell I31:

=IF(AND((OR(E31="Pipe",E31="Riser")),(NOT(H31=0))) ,"m",IF(AND((H311),(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.

Formula Help Needed
 

On 11 Iun, 06:04, puting_uwak
wrote:

This formula work for you ?

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

Formula Help Needed
 

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 H311.

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

Formula Help Needed
 

On 12 Iun, 07:35, puting_uwak wrote:
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 :

=IF(AND(OR((E31)="Pipe",(E31)="Riser"),(H31)<0)," m",IF(AND((H31)
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

Formula Help Needed
 

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 :
=IF(AND(OR((E31)="Pipe",(E31)="Riser"),(H31)<0)," m",IF(AND((H31)
1),"nos.",IF(AND((H31)=1,(E31)<""),"no.","")))

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

Formula Help Needed
 

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.

Formula Help Needed
 

On 13 Iun, 05:29, puting_uwak
wrote:
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

Formula Help Needed
 

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="")),N OT(H4=""),H41),"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!!!

Formula Help Needed
 

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="")),N OT(H4=""),H41),"nos.","")))
to
=IF(AND(OR(E4="Pipe",E4="Riser"),H4<""),"m",IF(AN D(E4<"Pipe",E4<"Riser",E4<"",H4=1),"no.",IF(AND (E4<"Pipe",E4<"Riser",E4<"",H4<"",H41),"nos." ,"")))
and could shorten it further.
--
David Biddulph

puting_uwak wrote:
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="")),N OT(H4=""),H41),"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!!!

Formula Help Needed
 

"David Biddulph" <groups [at] biddulph.org.uk wrote...
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<"",H41) ,"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.",""))))