Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF and OR nested statement help
Hi,
Think I'm nearly there with my requirement but can't seem to get it correct and into one formula!! Basically need to look for where "*** Created ***" or "*** Deleted ***" appears within column J and Column G contains "Bank Details". I've worked this formula out as per below: =IF(AND(G21="Bank Details"),(OR(J21="*** Deleted ***",J21="*** Created ***")),) However, I also need to have another IF statement in this formula for if the above isn't appearing but the first four letters of column J contains "Bank" then again show True. On it's own this formula would do this: =IF(FIND("Bank",J22)=1,TRUE,FALSE) How do I basically combine this into one formula? This would be for each row so obviously my example is just using the row underneath. In summary: - If entry in column J = *** Created *** OR = *** Deleted *** AND entry in column G = "Bank Details" then put true. - If first 4 characters within column J's entry = "Bank" then put true ELSE: FALSE Appreciate any help. Thanks, Al. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF and OR nested statement help
Does:
=IF(OR(LEFT(G21,4)="Bank",AND(G21="Bank Details"),(OR(J21="*** Deleted ***",J21="*** Created ***"))),TRUE,FALSE) do what you want? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk wrote in message ps.com... Hi, Think I'm nearly there with my requirement but can't seem to get it correct and into one formula!! Basically need to look for where "*** Created ***" or "*** Deleted ***" appears within column J and Column G contains "Bank Details". I've worked this formula out as per below: =IF(AND(G21="Bank Details"),(OR(J21="*** Deleted ***",J21="*** Created ***")),) However, I also need to have another IF statement in this formula for if the above isn't appearing but the first four letters of column J contains "Bank" then again show True. On it's own this formula would do this: =IF(FIND("Bank",J22)=1,TRUE,FALSE) How do I basically combine this into one formula? This would be for each row so obviously my example is just using the row underneath. In summary: - If entry in column J = *** Created *** OR = *** Deleted *** AND entry in column G = "Bank Details" then put true. - If first 4 characters within column J's entry = "Bank" then put true ELSE: FALSE Appreciate any help. Thanks, Al. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF and OR nested statement help
try:
=IF(OR(ISNUMBER(FIND("Bank",J21)),AND(G21="Bank Details"),(OR(J21="*** Deleted ***",J21="*** Created****"))),TRUE,FALSE) " wrote: Hi, Think I'm nearly there with my requirement but can't seem to get it correct and into one formula!! Basically need to look for where "*** Created ***" or "*** Deleted ***" appears within column J and Column G contains "Bank Details". I've worked this formula out as per below: =IF(AND(G21="Bank Details"),(OR(J21="*** Deleted ***",J21="*** Created ***")),) However, I also need to have another IF statement in this formula for if the above isn't appearing but the first four letters of column J contains "Bank" then again show True. On it's own this formula would do this: =IF(FIND("Bank",J22)=1,TRUE,FALSE) How do I basically combine this into one formula? This would be for each row so obviously my example is just using the row underneath. In summary: - If entry in column J = *** Created *** OR = *** Deleted *** AND entry in column G = "Bank Details" then put true. - If first 4 characters within column J's entry = "Bank" then put true ELSE: FALSE Appreciate any help. Thanks, Al. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF and OR nested statement help
Ahh... Thanks all. Last formula seems to do this, however I've now
got a further problem when looking @ the data set. It seems though looking at the data with the formulas a better way of sorting my data would be if I could also say if the line is the line with "Bank" as the first 4 characters in the cell J, then only show TRUE if the line above is the one that contains *** Created *** or *** Deleted *** Sorry to be a pain. Thanks for your help so far. Al. On 16 Jul, 21:54, Toppers wrote: try: =IF(OR(ISNUMBER(FIND("Bank",J21)),AND(G21="Bank Details"),(OR(J21="*** Deleted ***",J21="*** Created****"))),TRUE,FALSE) " wrote: Hi, Think I'm nearly there with my requirement but can't seem to get it correct and into one formula!! Basically need to look for where "*** Created ***" or "*** Deleted ***" appears within column J and Column G contains "Bank Details". I've worked this formula out as per below: =IF(AND(G21="Bank Details"),(OR(J21="*** Deleted ***",J21="*** Created ***")),) However, I also need to have another IF statement in this formula for if the above isn't appearing but the first four letters of column J contains "Bank" then again show True. On it's own this formula would do this: =IF(FIND("Bank",J22)=1,TRUE,FALSE) How do I basically combine this into one formula? This would be for each row so obviously my example is just using the row underneath. In summary: - If entry in column J = *** Created *** OR = *** Deleted *** AND entry in column G = "Bank Details" then put true. - If first 4 characters within column J's entry = "Bank" then put true ELSE: FALSE Appreciate any help. Thanks, Al.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to do a nested if statement | Excel Discussion (Misc queries) | |||
Nested IF Statement | Excel Discussion (Misc queries) | |||
Nested IF Statement | Excel Discussion (Misc queries) | |||
Nested IF Statement | Excel Discussion (Misc queries) | |||
Nested If statement | Excel Worksheet Functions |