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.

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.

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.

On 11 Iun, 06:04, puting_uwak
wrote:

This formula work for you ?

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

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

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

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

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.