Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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") |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. :) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.",""))) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Needed
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.","")))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel formula to copy/paste formula needed please. | Excel Discussion (Misc queries) | |||
Formula needed please... | New Users to Excel | |||
Formula Help needed | Excel Worksheet Functions | |||
Little more help needed for my IF formula | Excel Discussion (Misc queries) | |||
Formula Help Needed | Excel Discussion (Misc queries) |