Originally Posted by
Simon Lloyd
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>).
Originally Posted by
Simon Lloyd
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,
"")))
Originally Posted by
Simon Lloyd
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.
Reply With Quote
