Using AND and OR in same IF statement

[Ally]

Hi

I’m trying to do a convoluted IF AND OR statement and am getting in a bit of a mess.

I’m trying to say:

IF A cell = “Achieved”
	AND B cell = “Y”
	OR B cell = “N”
	OR B cell = “Not indicated”
Then value = “Achieved”
IF A cell = “Achieved”
	AND B cell = “Indicated?”
	OR B cell = “Invalid Date”
Then value = “Achieved but indicator or date error”
IF A cell = “Failed”
	AND B cell “Y”
	OR B cell = “N”
	OR B cell = “Not Indicated”
Then value = “Failed”
If A cell = “Failed”
	AND B cell = “Indicated?”
	OR B cell = “Invalid Date”
Then value = “Failed and indicator or date error”
… and there’s more in the same vane.

I know that I can only have 7 nested IF’s in 2003. I have tried many variations of
IF(AND(OR(…. But it either doesn’t work properly, or I have a syntax error or when I get to the second section, it’s not picking up the AND and only looking at the OR. I’ve tried putting extra AND(OR inbetween but that’s not working.

Can anyone help please?

Thanks

Ally

 

[the_net_2.0]

Hello Ally. Yes, these functions in Excel can get pretty wacky.
The AND() function is sort of like Switch() in Access. The way I understand it, all of the conditionals inside the brackets have to be met for TRUE. It returns one of two booleans. So does the OR() function, but the stipulation is that any of the conditionals must be met. If none of them are, FALSE comes back.

So, for your code, I wonder if this would work:

=if(a1="achieved", if(or(b1="y", b1="n", b1="not indicated")=true,

"achieved", if(or(b1="indicated?", b1="invalid date")=true, "achieved but...", "")), 

if(or(b1="y", b1="n", b1="not indicated")=true, "failed", 

if(or(b1="indicated?", b1="invalid date")=true, "failed and indicator...", "")))

I of course, abbreviated some of the code there, but that's the idea. The other thing is that, if there is more like you say, you should restructure your data very soon.

Sometimes when things get this conditionalised in Excel (to the point where you can't manage the possibilities), it's either time for more sheets or a move to Access. For instance, in Access, all of these possibilities would be stored in one table and referred to when necessary. No need for infinite nestings like this.

IF A cell = “Achieved”
	AND B cell = “Y”
	OR B cell = “N”
	OR B cell = “Not indicated”
Then value = “Achieved”
IF A cell = “Achieved”
	AND B cell = “Indicated?”
	OR B cell = “Invalid Date”
Then value = “Achieved but indicator or date error”
IF A cell = “Failed”
	AND B cell “Y”
	OR B cell = “N”
	OR B cell = “Not Indicated”
Then value = “Failed”
If A cell = “Failed”
	AND B cell = “Indicated?”
	OR B cell = “Invalid Date”
Then value = “Failed and indicator or date error”
… and there’s more in the same vane.

 

[Brianwarnock]

You might like to condider a function below is a brief sample

Function selectcase(ra As Range, rb As Range) As String
avalue = ra.Value
bvalue = rb.Value
Select Case avalue
Case "Achieved"
    
  Select Case bvalue
    Case "Y", "N"
        selectcase = "Achieved"
    Case "Indicated", "Invalid Date"
        selectcase = "Achieved but indicator or date error"
    End Select
Case "Failed"
    Select Case bvalue
    Case "Y", "N"
        selectcase = "Failed"
    Case "Indicated", "Invalid Date"
        selectcase = "Failed and indicator or date error"
    End Select
Case Else
    selectcase = "query data colA"
End Select
   
End Function

In say C1 enter =selectcase(A1,B1), like all functions you can copy down
Note that the Case statement in the code is case sensitive, achieved would not be picked up for example.

Brian

Ps I should have declared avalue and bvalue eg
Dim avalue as string

 

[the_net_2.0]

You might like to condider a function below is a brief sample

very nice Brian. thank you for posting that!

 

[Ally]

Hi both Brian and the Net!

Thank you very much. The If's definitely work, and I'm going to have a play with the VBA in Excel soon!

Everything's (almost) so much simpler in Access!!