PDA

View Full Version : 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
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?

xld
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,
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.

Regards,
Simon

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

xld
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

xld
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?

Simon Lloyd
12-03-2006, 03:26 PM
Ok Bob i'll make it as english as i can but it's quite a mess in my head at times!

Selections Available:
LCO, Line Check, Line Clean, Line Off, Line On, No Ops, Re-pack, Ln Maintainance

The Formula Cell result shoud be value of B31 if:
C2= "No Ops" G2="No Ops"
C2= "Line Off" G2="Line Off"
C2= "No Ops" G2="Line Off"......this is the criteria that causes FALSE
C2= "Line Off" G2="No Ops".......this is the criteria that causes FALSE
And D4=""

The Formula Cell result shoud be "" if:
C2= "Line On" G2= ANY OTHER SELECTION
C2= ANY OTHER SELECTION G2="Line On"
And D4=""

The Formula Cell result shoud be "" if:
C2= ANY SELECTION
G2= ANY SELECTION
And D4 ="" (except first set of results i.e Line off Line off etc)

The Formula Cell result shoud be "" if:
C2= ANY SELECTION
G2= ANY SELECTION
And D4 <>""

=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)))))

this works fine except for the criteria pointed out in the first results criteria!

Hope this helps,

Regards,
Simon

P.S Thanks for your patience Bob

Gert Jan
12-03-2006, 04:08 PM
Just to make sure, does this do the first part?

The Formula Cell result shoud be value of B31 if:
C2= "No Ops" G2="No Ops"
C2= "Line Off" G2="Line Off"
C2= "No Ops" G2="Line Off"......this is the criteria that causes FALSE
C2= "Line Off" G2="No Ops".......this is the criteria that causes FALSE
And D4=""

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

xld
12-03-2006, 05:05 PM
I may be over-simplifying it, but it just looks like this to me

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

Simon Lloyd
12-04-2006, 01:01 AM
Gert that is correct!

Bob - With your usual clarity hit the nail on the head for the first part!, I cant believe i went so complicated!

Now i need to incorporate the next criteria - so with the combination you put together in your last post if these cells have these values
J5=2,L5=2,N5=2,R5=2,T5=2,V5=2and D4="" then cell should display blank - this is because the Ops will be used at another line.

I did try combinations of this
=IF(AND(D4="",AND(OR(C2={"No Ops","Line Off"}),OR(G2={"No Ops","Line Off"}))),B31,"",if(and(D4="",if(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31)))but either got the message "Too Many Arguments" Or " You Have Typed An Error" or i could get it to leave the contents of B31 in the cell regardless of any other criteria.

Regards,
SImon

Simon Lloyd
12-04-2006, 02:39 AM
Bob - All,

I have managed to get the two formulae together and it works perfect, Bob thanks for your time and trouble last night on this!

Final Formula:
=IF(AND(D4="",AND(OR(C2={"No Ops","Line Off"}),OR(G2={"No Ops","Line Off"}))),IF(AND(D4="",AND(OR(C2={"No Ops","Line Off"}),OR(G2={"No Ops","Line Off"}))),IF(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31)),IF(AND(OR(C2={"Line On","Line On"})),"",""))

Thanks again to all.

regards,
Simon:doh:

xld
12-04-2006, 02:49 AM
Bob - With your usual clarity hit the nail on the head for the first part!, I cant believe i went so complicated!
Simon, I think that the problem is that you try and construct a formula that meets the first condition, then add on the next, then the next, and so on.

By writing it down as you did, you can see the pattersn, and construct an OVERALL solution. Like all things, formulae benefit from a bit of thought and design (45 minutes into the formulae course <G>).

Now i need to incorporate the next criteria - so with the combination you put together in your last post if these cells have these values and D4="" then cell should display blank - this is because the Ops will be used at another line.
First, let's construct an English view as before

D4=""
C2= "No Ops" G2="No Ops"
C2= "Line Off" G2="Line Off"
C2= "No Ops" G2="Line Off"......this is the criteria that causes FALSE
C2= "Line Off" G2="No Ops".......this is the criteria that causes FALSE
>>> Result: B31

C2= "Line On" G2= ANY OTHER SELECTION
C2= ANY OTHER SELECTION G2="Line On"
>>> Result: ""

C2= ANY SELECTION
G2= ANY SELECTION
>>> Result: ""

J5= 2 OR L5=2 OR N5=2 OR R5=2 OR T5=2 OR V5=2
C2= ANY SELECTION (inc. the first test values)
G2= ANY SELECTION (inc. the first test values)
>>> Result: ""

D4 <>""
C2= ANY SELECTION
G2= ANY SELECTION
>>> Result: ""

Assuming that I have correctly interpreted the criteria, the first thing that hits me is the

C2= ANY SELECTION (inc. the first test values)

in the new test, which suggests that we cannot add a simple extra IF after the other tests, as the previous criteria will have already set the result, so we need to do it first. All we need is to add

IF(AND(D4="",OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2)),"",

to the formula, giving

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

One thing to notice is that both of the first two tests check for D4="", so we could abstract this

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

I did try combinations of this

=IF(AND(D4="",AND(OR(C2={"No Ops","Line Off"}),OR(G2={"No Ops","Line Off"}))),B31,"",if(and(D4="",if(OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2),"",B31))) but either got the message "Too Many Arguments" Or " You Have Typed An Error" or i could get it to leave the contents of B31 in the cell regardless of any other criteria.
The problem here is that you tried to add a condition after the formula was complete, that is the first condition had a TRUE action (B31) and a FALSE action (""), so you cannot just tag another test after that, you have to replace the FALSE action.

Doing that still gives an error, as you have used AND(D4="" and then added IF which is redundant, you should use

IF(AND(D4="",OR(J5=2,L5=2,N5=2,R5=2,T5=2,V5=2)),

which gives a final formula of

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

but as I said earlier, this doesn't fail but it doesn't work because that final condition never gets tested, the other conditions would return a result previously.

Simon Lloyd
12-04-2006, 03:09 AM
Thanks for the lesson Bob,
formulae benefit from a bit of thought and design (45 minutes into the formulae course <G>).
Wow!, i'd be galloping to the last fence by then with half a horse!!!! but you should know me by know - fingers start working a couple of hours before the brain does! Lol.

I did kind of figure out that in order to "Tag" another formula on the end i had to break the end of the formula - so in actual fact i was using my next statement as the True part of the last statement _ but as you pointed out i would still run in to difficulty!

My last formula posted seems to work in every respect, have i stumbled on to the correct way of doing it or is it just a lucky combination?

Regards,
Simon

xld
12-04-2006, 03:38 AM
My last formula posted seems to work in every respect, have i stumbled on to the correct way of doing it or is it just a lucky combination?
I hadn't seen this when I posted.

Just reformat the formula and take a look at it

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

The first test is immediately repeated, and the last test does an OR on the same condition, and then sets it to "" whether TRUE or FALSE.

Such redundancy can get very expensive on large, complicated spreadsheets, it all is examined on a sheet recalculation, and can slow the calculation down considerably.

Please don't take this the wrong way, it is offered as constructive criticisim, but I think that you struggle with formula logic. IMO you would benefit from putting the problem on paper and trying to resolve the logic before you commit the formula into Excel, do some decision type tables, or flowcharting, and try and make it as tight as possible before putting in Excel. I also think my formula formatting could help, you see thinsg much more clearly.

Aussiebear
12-04-2006, 03:53 AM
Good point for all of us Bob.

xld
12-04-2006, 04:02 AM
Good point for all of us Bob.

Indeed, I am a practitioner myself.

Simon Lloyd
12-04-2006, 04:08 AM
Bob it takes alot to offend me!, getting anyway getting help, information or criticism from peolple like you is a privilidge not a put down!

I understand what you have done in the re-format, i pasted my formula in to notepad (as notpad puts everything in to one long line!) and i could see my "copying error" of the two exact same tests at the begining, the statement at the end was a typo it should have read
IF(AND(OR(C2="Line On",G2="Line On")),"","")) as i was getting a value in the cell if Line On appeared, this seemed to solve it and all worked ok.

You have a valid point of course and you will see from my many posts over time that i sort of build and re-think as i go along - it can be tedious and frustrating not just for me but you guys too!

The last condition BTW is set to "" whether TRUE or FALSE as i needed a blank if it showed Line on but if it didnt show Line on then the rest of the formula was true - ineptitude on my part!

Regards,
Simon

xld
12-04-2006, 04:22 AM
I understand what you have done in the re-format, i pasted my formula in to notepad (as notpad puts everything in to one long line!) ...

What, you still use Notepad? Why?

There are dozens of better text editors out there, many free. Examples
vim
emacs
Notetab Light

Simon Lloyd
12-04-2006, 04:53 AM
Thanks - For home i will look at those , but work don't allow many types of downloads and of course there is ever the problem with licences and corporate business!

Regards,
Simon

xld
12-04-2006, 05:05 AM
Thanks - For home i will look at those , but work don't allow many types of downloads ...

Even for open code such as SourceForge?

and of course there is ever the problem with licences and corporate business!

Not if it is freeware.

Simon Lloyd
12-04-2006, 06:44 AM