Solved: IF(AND(OR Formula Help Take 2!

**Simon Lloyd** · 12-03-2006, 01:35 AM

Hi all, as a follow on from a previous post {(IF(AND(OR Formula Help}, i have created a formula (which doesnt work, shows Value#) to look at the state of two cells and display contents of another, If other cells in the formula have a value then display blank, my problem arises when trying to show a blank for "s"(ick) or "sw"(apped).

Here's what i have (take a breath!)
=IF(AND(C2="No Ops",G2="No Ops"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31),IF(AND(C2="Line Off",G2="Line Off"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31),IF(AND(C2="Line Off",G2="Line Off",),B31,""))),IF(AND(C2="No Ops",G2="No Ops",OR(D4="s",D4="sw")),"",B31),IF(AND(C2="Line Off",G2="Line Off",OR(D4="s",D4="sw")),"",B31),IF(AND(C2="No Ops",G2="Line Off",OR(D4="s",D4="sw")),"",B31),IF(AND(C2="No Ops",G2="Line Off",OR(D4="s",D4="sw")),"",B31)

as you can see there are 9 IF statements (i think 7 is the maximum!)

I have tried diffetent combinations, i have tried IF(AND(An="x",OR but couldnt get it to accept the formula as it seems i had too many arguments.

Hope you can help!

Regards,
SImon

**Aussiebear** · 12-03-2006, 01:55 AM

Is there a way of splitting this formula into 2 routines?

**Simon Lloyd** · 12-03-2006, 03:17 AM

Aussie, i guess it could be split up like so:
=IF(AND(C2="No Ops",G2="No Ops"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31),IF(AND(C2="Line Off",G2="Line Off"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31),IF(AND(C2="Line Off",G2="Line Off",IF(AND(C2="No Ops",G2="No Ops",),B31,""))))

The above routine works fine (give or take some copying errors)

IF(AND(C2="No Ops",G2="No Ops",OR(D4="s",D4="sw")),"",B31),IF(AND(C2="Line Off",G2="Line Off",OR(D4="s",D4="sw")),"",B31),IF(AND(C2="No Ops",G2="Line Off",OR(D4="s",D4="sw")),"",B31),IF(AND(C2="No Ops",G2="Line Off",OR(D4="s",D4="sw")),"",B31)

The above routine is where the problem lies, in the first routine if cells C2 And G2 display the same contents then the formula cell displays B31 if one of the other cells in the formula contain 2 then formula cell = "", but in this routine i am trying to cover the eventuallity of cells C2 and G2 displaying anything and D4 containing S or SW then the formula cell should be blank.

It needs to work with these :
LCO, Line Check, Line Clean, Line Off, Line On, No Ops, Re-pack, Ln Maintainance

Summary: If C2 AND G2 display these combinations without a value in D4 then display contents of B31
.....C2........G2
Line Off Line Off
No Ops No Ops
Line Off No Ops
No Ops Line Off

If C2 OR G2 display any of these LCO, Line Check, Line Clean, Line Off, Line On, No Ops, Re-pack, Ln Maintainance AND D4 = S OR SW then display nothing.

Regards,
Simon

**Aussiebear** · 12-03-2006, 03:41 AM

Simon, Its a clear as mud to me, however I do understand the limitations of the nested if's. Somewhere in my directionless meanderings I thought I came across a suggestion where it was possible to get around the limitation of 7.

Not sure but it might have been a method suggested by Dave Hawley at OZGrid.com.au

My apologies to those who run this site for suggesting an alternative excel site

**Simon Lloyd** · 12-03-2006, 03:47 AM

Aussie its ok have used a lot of forums including that one, not wishing to sound like im sucking up but the cleverest people with regards to complex problems are here!

Its not so much as i need a work around i just dont have the knowledge or experience of formula writing to attempt a sensible concatenation of them!

Here's what i have for the part that says if D4 <>"" then this cell ="" although instead of showing either the contents of B31 or blank it displays FALSE

=IF(AND(D4="s"),IF(OR(C2="No Ops",C2="Line Off",C2="Line Clean",C2="LCO",C2="Ln Maintainance",C2="Re-Pack",C2="Line Check",C2="Line ON",G2="No Ops",G2="Line Off",G2="Line Clean",G2="LCO",G2="Ln Maintainance",G2="Re-Pack",G2="Line Check",G2="Line ON"),IF(AND(D4="sw"),IF(OR(C2="No Ops",C2="Line Off",C2="Line Clean",C2="LCO",C2="Ln Maintainance",C2="Re-Pack",C2="Line Check",C2="Line ON",G2="No Ops",G2="Line Off",G2="Line Clean",G2="LCO",G2="Ln Maintainance",G2="Re-Pack",G2="Line Check",G2="Line ON"),"",B31))))

However i still need this routine:
=IF(AND(C2="No Ops",G2="No Ops"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31),IF(AND(C2="Line Off",G2="Line Off"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31),IF(AND(C2="Line Off",G2="Line Off",IF(AND(C2="No Ops",G2="No Ops",),B31,""))))

incorporated in to it!

Regards,
Simon

**Simon Lloyd** · 12-03-2006, 04:03 AM

UPDATE: =IF(AND(D4<>""),IF(OR(C2="No Ops",C2="Line Off",C2="Line Clean",C2="LCO",C2="Ln Maintainance",C2="Re-Pack",C2="Line Check",C2="Line ON",G2="No Ops",G2="Line Off",G2="Line Clean",G2="LCO",G2="Ln Maintainance",G2="Re-Pack",G2="Line Check",G2="Line ON"),"",B31)))

This is what i got by using D4<>"" but i still get the value FALSE

i still need this routine:
=IF(AND(C2="No Ops",G2="No Ops"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31),IF(AND(C2="Line Off",G2="Line Off"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31),IF(AND(C2="Line Off",G2="Line Off",IF(AND(C2="No Ops",G2="No Ops",),B31,""))))

incorporated in to it!
Regards,
Simon

**Simon Lloyd** · 12-03-2006, 04:18 AM

UPDATE 2: =IF(AND(D4<>""),IF(OR(C2="No Ops",C2="Line Off",C2="Line Clean",C2="LCO",C2="Ln Maintainance",C2="Re-Pack",C2="Line Check",G2="No Ops",G2="Line Off",G2="Line Clean",G2="LCO",G2="Ln Maintainance",G2="Re-Pack",G2="Line Check"),"",B31),IF(AND(C2="No Ops",G2="No Ops"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31),IF(AND(C2="Line Off",G2="Line Off"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31))))

The above works in reverese or it seems to!, if D4 = "" then value is FALSE if D4<>"" value displayed = B31?

Value B31 should only be seen when both C2 & G2 = Line Off or No Ops but then it should not be seen if one of the other cells in the formula have a value 2 (which works ok)

Regards,
Simon

P.S isn't great when you start to overcome obstacles?

**Simon Lloyd** · 12-03-2006, 05:08 AM

UPDATE 3: =IF(AND(D4<>""),IF(OR(C2="No Ops",C2="Line Off",C2="Line Clean",C2="LCO",C2="Ln Maintainance",C2="Re-Pack",C2="Line Check",C2="Line On",G2="No Ops",G2="Line Off",G2="Line Clean",G2="LCO",G2="Ln Maintainance",G2="Re-Pack",G2="Line Check",G2="Line On"),"",""),IF(AND(C2="No Ops",G2="No Ops"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31),IF(AND(C2="Line Off",G2="Line Off"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31))))

This works ALMOST correctly!, it will show blank if D4 <> "" and any combination in cells C2, G2......But if C2 AND OR G2 show "Line On" then it displays the value FALSE.

Anyone know how to get it to show ""?

Regards,
SImon

**Simon Lloyd** · 12-03-2006, 05:19 AM

FINAL UPDATE: =IF(AND(D4<>""),IF(OR(C2="No Ops",C2="Line Off",C2="Line Clean",C2="LCO",C2="Ln Maintainance",C2="Re-Pack",C2="Line Check",C2="Line On",G2="No Ops",G2="Line Off",G2="Line Clean",G2="LCO",G2="Ln Maintainance",G2="Re-Pack",G2="Line Check",G2="Line On"),"",""),IF(AND(D4=""),IF(OR(C2="Line On",G2="Line On"),"",IF(AND(C2="No Ops",G2="No Ops"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31),IF(AND(C2="Line Off",G2="Line Off"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31))))))

This works perfectly or seems to, may need a little more testing yet!

It has only taken 4hrs 20mins to find the solution! Phew!

If you do have any smarter formulas for this or notice something wrong at all please let me know.

Regards,
Simon

**Simon Lloyd** · 12-03-2006, 05:46 AM

Guess i'm not that clever after all!

=IF(AND(D4<>""),IF(OR(C2="No Ops",C2="Line Off",C2="Line Clean",C2="LCO",C2="Ln Maintainance",C2="Re-Pack",C2="Line Check",C2="Line On",G2="No Ops",G2="Line Off",G2="Line Clean",G2="LCO",G2="Ln Maintainance",G2="Re-Pack",G2="Line Check",G2="Line On"),"",""),IF(OR(C2="Line On",G2="Line On"),"",IF(AND(C2="No Ops",G2="No Ops"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31),IF(AND(C2="Line Off",G2="Line Off"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31)))))

With the formula above everything works great until i choose No Ops in either C2 or G2 and LIne Off in either C2 or G2, that is to say i.e G2="No Ops" C2= "Line Off" i get the value false and vice versa, i could sort it out with another IF statement but i have used 7 already......Definately need expert help with this one!

When the combinations above are entered the value displayed should be B31 not FALSE

Regards,
Simon

**Gert Jan** · 12-03-2006, 06:00 AM

Hi Simon,
Lookin' at your formula i'm completly lost, but about nesting more then 7 statements, i've seen something like this on the site from
Ingrid Baplue http://users.pandora.be/ingrid/excel/namen.htm#formule.
It's about defining a name to formulae. It is written in Dutch, but by looking at the examples maybe you'll get an idea of wat she is trying to tell.
I don't know if this is what you're lookin for, but maybe this will get you(or anyone else) in the direction you want?

Gert Jan

**Simon Lloyd** · 12-03-2006, 08:57 AM

Gert thanks for that, looking at my formula i would have said i can read dutch!, but i am hoping i dont have to use a workaround, maybe some one can incorporate the last terms for me!

Rgards,
Simon

**Gert Jan** · 12-03-2006, 10:24 AM

Originally Posted by Simon Lloyd

Summary: If C2 AND G2 display these combinations without a value in D4 then display contents of B31
.....C2........G2
Line Off Line Off
No Ops No Ops
Line Off No Ops
No Ops Line Off

If C2 OR G2 display any of these LCO, Line Check, Line Clean, Line Off, Line On, No Ops, Re-pack, Ln Maintainance AND D4 = S OR SW then display nothing.

So, when there's nothing in D4, it's always B31?

**Bob Phillips** · 12-03-2006, 10:36 AM

I think that this is what you want

=IF(OR(
            AND(
                    OR(
                          AND(C2="No Ops",G2="No Ops"),
                          AND(C2="Line Off",G2="Line Off")
                         ),
                    OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2)
                   ),
                    AND(
                            OR(C2="No Ops",G2="No Ops",C2="Line Off",G2="Line Off"),
                            OR(D4="s",D4="sw")
                           )
                  ),
      "",B31)

**Simon Lloyd** · 12-03-2006, 12:33 PM

Gert,

Originally Posted by Gert

So, when there's nothing in D4, it's always B31?

no thats not the case because if C2 & G2 do not display Lne Off Or No Ops then it should display blank.

My formula works entirely well, except for the anomaly i found when selecting No Ops in Either C2 or G2 and Line Off in the other it displayed a value of FALSE.

Bob, thanks for the reply and the time and trouble to try and sort the muddle, as you can see from my progressive posts that i made quite a bit of headway on my own eventually having to use 7 statements and then was flumoxed when coming across the above problem....I have to say i'm not entirely sure what to do with your post?, maybe its the exploded view thats leading me astray! my formula starts with IF(AND yours with IF(OR

The reason for the dual criteria

IF(AND(C2="No Ops",G2="No Ops"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31),IF(AND(C2="Line Off",G2="Line Off"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31)))))

is because the Ops will never move unless sick, swapped or both lines are off or no ops, then if any of the other cells in the formula contain the value 2 show blank.

Can you walk me through your suggestion please?

Regards,
Simon

**Bob Phillips** · 12-03-2006, 01:32 PM

Simon,

I started with your original formula

=IF(AND(C2="No Ops",G2="No Ops"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31),IF(AND(C2="Line Off",G2="Line Off"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31),IF(AND(C2="Line Off",G2="Line Off",),B31,""))),IF(AND(C2="No Ops",G2="No Ops",OR(D4="s",D4="sw")),"",B31),IF(AND(C2="Line Off",G2="Line Off",OR(D4="s",D4="sw")),"",B31),IF(AND(C2="No Ops",G2="Line Off",OR(D4="s",D4="sw")),"",B31),IF(AND(C2="No Ops",G2="Line Off",OR(D4="s",D4="sw")),"",B31)

First I re-formatted it to make it readable

=IF(AND(C2="No Ops",G2="No Ops"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31
   (AND(C2="Line Off",G2="Line Off"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31),
IF(AND(C2="Line Off",G2="Line Off",),B31,""))),
IF(AND(C2="No Ops",G2="No Ops",OR(D4="s",D4="sw")),"",B31),
IF(AND(C2="Line Off",G2="Line Off",OR(D4="s",D4="sw")),"",B31),
IF(AND(C2="No Ops",G2="Line Off",OR(D4="s",D4="sw")),"",B31),
IF(AND(C2="No Ops",G2="Line Off",OR(D4="s",D4="sw")),"",B31)

From this I could see that you were testing C2 and G2 for all combinations of "No Ops" and "Line Off", so I could simplify that to just OR statements, and combine that with the D4 OR, so

IF(AND(C2="No Ops",G2="No Ops",OR(D4="s",D4="sw")),"",B31),
IF(AND(C2="Line Off",G2="Line Off",OR(D4="s",D4="sw")),"",B31),
IF(AND(C2="No Ops",G2="Line Off",OR(D4="s",D4="sw")),"",B31),
IF(AND(C2="No Ops",G2="Line Off",OR(D4="s",D4="sw")),"",B31)

can be simplified to

'test#2
                    AND(
                            OR(C2="No Ops",G2="No Ops",C2="Line Off",G2="Line Off"),
                            OR(D4="s",D4="sw")
                           )

Similalrly,

IF(AND(C2="No Ops",G2="No Ops"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31
   (AND(C2="Line Off",G2="Line Off"),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31),
IF(AND(C2="Line Off",G2="Line Off",),B31,"")))

can (just about) be simplified to

'test#1
            AND(
                    OR(
                          AND(C2="No Ops",G2="No Ops"),
                          AND(C2="Line Off",G2="Line Off")
                         ),
                    OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2)
                   ),

As you want either or of these conditions, and as both return blank or B31, they can then be ORed

=IF(OR(
           test#1,
           test#2
          ),
      "",B31)

Which all combined gives us

=IF(OR(
            AND(
                    OR(
                          AND(C2="No Ops",G2="No Ops"),
                          AND(C2="Line Off",G2="Line Off")
                         ),
                    OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2)
                   ),
                    AND(
                            OR(C2="No Ops",G2="No Ops",C2="Line Off",G2="Line Off"),
                            OR(D4="s",D4="sw")
                           )
                  ),
      "",B31)

BTW, the formula in that exploded view can still be copied to the formula bar and it works fine.

**Aussiebear** · 12-03-2006, 02:05 PM

That's a great post Bob. Your steps are explained in detail and very easy to follow.

**Bob Phillips** · 12-03-2006, 02:15 PM

Thanks Ted.

**Simon Lloyd** · 12-03-2006, 02:29 PM

Bob nicely explained! clear and concise as ever, however im sorry it doesnt work for the criteria!

If you could take a look at my formula that i posted just before Gert posted, that formula does all that i need except that if i choose C2 or G2 to be one as No Ops and one as Line Off then the formula shows FALSE when it should display B31 and i had no IF's left in the bag to cure this.

your code as you posted it regardless of the states of C2 & G2 shows B31 as the result.

Regards,
Simon

**Bob Phillips** · 12-03-2006, 03:03 PM

Simon,

Looking at that formula, it doesn't seem to equate to what you said.

This

IF(OR(C2="Line On",G2="Line On"),"",
IF(AND(C2="No Ops",G2="No Ops"),

isn't testing for C2 = No Ops and G2 = Line Off or vice versa.

Can you give an English summary of what should happen, and prefereably a list like before. Also, what condition were you trying to add to the previous working version?

Thread: Solved: IF(AND(OR Formula Help Take 2!

Thread Tools

Display

Solved: IF(AND(OR Formula Help Take 2!

Posting Permissions