Solved: IF(AND(OR formula help!

[Simon Lloyd]

Hi all i am trying to create a formula that looks at several criteria
1. IF F4="S","Sick"
2. IF G2="Line Off",""
3. IF G2="No Ops",""
4. IF F4="SW","Swapped"
If the above are not true then B32
Next
5. IF F4="SW" and G2="Line Off",
6. IF F4="SW" and G2="No Ops"
If the above are true then "Swapped" or of course the cell should show blank if only criteria 2 OR 3 are met.
Next
7. IF F4="S" and G2="Line Off",
8. IF F4="S" and G2="No Ops"
If the above are true then "Sick" or show blank
Here's what i have that doesnt work
=IF(F4="S","Sick",IF(G2="Line Off","",IF(G2="No Ops","",B32))),IF(F4="SW",IF(OR(G2="Line Off",G2="No Ops"),"Swapped",""))

Can you help?

Regards,
Simon

[Bob Phillips]

Simon,

Those criteria seem a tad jumbled.

4 says it is Swapped if F4 is SW, but 5 and 6 say it is Swapped if F4 = SW and etc., but here is a shot

=IF(F4="S","Sick",
IF(F4="SW",
IF(OR(G2="Line Off",G2="No Ops"),"Swapped",""),B32))

[Simon Lloyd]

Thanks Bob, Sorry for the jumbled mess that spouted from my fingertips! I have made an amendment as shown in blue

=IF(F4="S","Sick",IF(G2="No Ops","",IF(F4="SW",IF(OR(G2="Line Off",G2="No Ops"),"Swapped",""),B32)))

This formula works fine until i choose G2= "No Ops" AND F4= "SW" the cell that the formula resides in is blank but should display "Swapped"

The scenario G2= "Line Off" AND F4= "SW" works fine also the scenario G2= "No Ops" AND F4= "S" works fine, the scenario G2= "Line Off" AND F4= "S" works fine too!

What i need is if G2 only displays "No Ops" or "Line Off" then the cell the formula is in should be blank, if then F4 should show either "S" or "SW" as well as the two states of G2 i just mentioned then the cell should show either "Sick" or "Swapped" respectively.

Regards,
Simon

[Simon Lloyd]

Bob....sorry forget that!

It was my "I can do attitude" that caused the problem!

The things that dont work are the other criteria in G2 like LCO or Line Clean etc that dont work with the combination of SW they work with S.

Was that as clear as mud?

Regards,

Simon

EDIT: Attachment added : Screenshot

[mdmackillop]

Hi Simon,
Multiple Ifs confuse me very quickly, so here's a UDF proposal. I don't know if I have all the logic right, but I think it's easier to manipulate.
[vba]
Option Explicit
Option Compare Text
Function MyIfs(F As Range, G As Range)
Dim Test As Boolean
Select Case F
Case "SW"
Select Case G
Case "LCO", "Line Check", "Line on", "Re-pack", _
"Line Clean", "Ln Maintenance"
MyIfs = "Blank"
Case Else
MyIfs = Range("B32")
End Select
Case "S"
Select Case G
Case "Line Off", "No Ops"
MyIfs = "Sick"
Case Else
MyIfs = "Blank"
End Select
End Select
End Function

[/vba]

[Bob Phillips]

Any better?

=IF(AND(G2<>"Line Off",G2<>"No Ops"),B32,
IF(F4="S","Sick",IF(F4="SW","Swapped","")))

[Simon Lloyd]

Malcom thanks for the response and if i cant get a sheet formula to do the job then i will certainly try that out!, however this is a new venture for me using worksheet functions as i always turn to VBA to solve my problems.

Bob- the formula worked in as much as it worked with Line Off and No Ops but it also needs to work with these :
LCO, Line Check, Line Clean, Line Off, Line On, No Ops, Re-pack, Ln Maintainance
If these appear in G2 without anything appearing in F4 then the result in the formula cell should be B32, if G2 has any off the above and F4 has "S" or "SW" then the formula cell should display Sick or Swapped.

Sorry for any confusion!

Regards,
Simon

[Bob Phillips]

Simon,

list the combinations and expected result please.

[Simon Lloyd]

List as requested:
G2 =: ........................F4=: ........................................Formula Cell Result:
No Ops .......................Nil................................................. ....... Nil
Line Check .................Nil ........................................................B32
Line Off .....................Nil .........................................................Nil
LCO ..........................Nil ........................................................B32
Ln Maintainance ..........Nil ........................................................B32
Re-Pack ....................Nil .........................................................B32
Line On .....................Nil .........................................................B32
Line Clean .................Nil .........................................................B32

No Ops..................... s ...........................................................Sick
Line Check ................s ...........................................................sick
Line Off ....................s ...........................................................Sick
LCO .........................s ...........................................................Sick
Ln Maintainance .........s ...........................................................Sick
Re-Pack ....................s ..........................................................Sick
Line On .....................s ..........................................................Sick
Line Clean .................s ..........................................................Sick

No Ops ....................sw ......................................................Swapped
Line Check............... sw ......................................................Swapped
Line Off ...................sw ......................................................Swapped
LCO ........................sw ......................................................Swapped
Ln Maintainance ........sw ......................................................Swapped
Re-Pack ..................sw .......................................................Swapped
Line On ...................sw .......................................................Swapped
Line Clean ................sw ......................................................Swapped

Hope this helps!
Regards,
Simon

[Simon Lloyd]

Sorry tried supplying this in a kind of table format but it reverted to as seen,
with all names in G2 if F4 has S or SW then result should be Sick or Swapped.

With all names in G2 if there is no value in F4 then result is B32 except Line Off and No Ops.

Regards,
SImon

[Aussiebear]

There are four distinct sub formulas here as I see it;

If F4 = "SW" and G2 <>"", "Swapped"

If F4 = "S" and G2<>"", Then "Sick"

If F4 = "" and G2 = "No Ops" or "Line Off",""

If F4 = "" and G2 = "Line Check" or "LCO" or "Ln Maintenance" or"Re Pack" or or "Line On" or "Line Clean" then B32

Am I right here?

[Bob Phillips]

=IF(AND(F4="SW",OR(G2="No Ops",G2="Line Check",G2="Line Off",G2="LCO",G2="Ln Maintainance",G2="Re-Pack",G2="Line On",G2="Line Clean")),"Swapped",
IF(AND(F4="S",OR(G2="No Ops",G2="Line Check",G2="Line Off",G2="LCO",G2="Ln Maintainance",G2="Re-Pack",G2="Line On",G2="Line Clean")),"Sick",
IF(OR(G2="No Ops",G2="Line Off"),B32,"")))


=========================================
Formula developed with Excel 2007
=========================================

[Simon Lloyd]

Aussie yes you were right, Bob thanks for that turned out to be a very long formula! i made a slight mod to get it to work as expected but does the job great!

Thanks again.

Regards,
Simon

MOD: =IF(AND(F4="SW",OR(G2="No Ops",G2="Line Check",G2="Line Off",G2="LCO",G2="Ln Maintainance",G2="Re-Pack",G2="Line On",G2="Line Clean")),"Swapped",IF(AND(F4="S",OR(G2="No Ops",G2="Line Check",G2="Line Off",G2="LCO",G2="Ln Maintainance",G2="Re-Pack",G2="Line On",G2="Line Clean")),"Sick",IF(OR(G2="No Ops",G2="Line Off"),"",B32)))

[mdmackillop]

[mdmackillop]

Hi Simon,
I came across this and thought you might find a use for it.
Regards
MD

Created by Chip Pearson

The IF function has a limit of 7 nested arguments. Chip's formula
circumvent that inherent limitation.

Define this formula as OneToSix:

=IF($A$4=1,11,IF($A$4=3,22,IF($A$4=5,33,IF($A$4=7,44,IF($A$4=9,55,IF
($A$4=11,44,IF($A$4=13,55,IF($A$4=15,66,FALSE))))))))

and this formula as SevenToThirteen:

=IF($A$4=17,77,IF($A$4=19,88,IF($A$4=21,99,IF($A$4=23,100,IF($A$4=25,110,
IF($A$4=27,120,IF($A$4=29,130,"NotFound")))))))

The combined formula looks like this:

=IF(OneToSix,OneToSix,SevenToThirteen)

[Simon Lloyd]

Malcom, I don't know what to say.....thanks for thinking of me!

I will save that procedure because the way things are going im going to need that!

As Bob said in another thread, i need to spend more time planning so i dont have to keep adding and constantly re-inventing!

Regards,
Simon