Embedded IF AND OR Formula

[J Morrow]

Team,

Please see attached. The upper section is my testing area for the formulas and the lower is the desired outcome. For the most part, the formula works with dates listed in the corresponding cells. However, there will be many cases of which instead of a date in column I (Calib Date), there will be entries of "NA" to indicate a calibration is not required. This is where my formula and additional attempts to tweak it fails such that I cannot obtain the desired status word in column E (Current Status) which contains the formulas.

Your help would be greatly appreciated.

[Bob Phillips]

Isn't it just

=IF(I3="NA","",IF(F3="Red Tagged",F3,
IF(OR(AND($D$1>G3+365,$D$1>H3+365,$D$1>J3+365),AND($D$1<G3+365,$D$1>H3+365),AND($D$1<G3+365,$D$1>H3+365,$D$1<J3+365)),"PM2",
IF(OR(AND($D$1>G3+365,$D$1<H3+365),AND($D$1>G3+365,$D$1<H3+365,$D$1<I3+30),AND($D$1>G3+365,$D$1>H3+365,$D$1<J3+365),AND($D$1>G3+365,$D$1<H3+365,$D$1>I3+30,$D$1<J3+365)),"PM1",
IF(OR(AND($D$1<G3+365,$D$1<H3+365,$D$1>I3+30),AND($D$1<G3+365,$D$1<H3+365,$D$1>I3+30,$D$1<J3+365)),"Calib",
IF(AND($D$1<G3+365,$D$1<H3+365,$D$1<I3+30,J3<I3),"String Check",
IF(AND($D$1<G3+365,$D$1<H3+365,$D$1<I3+30,J3>=I3),"Ready","")))))))

[J Morrow]

Bob Phillips,

I wish it were just that. However, that it not the case. To test the formula, unter "NA" without the quotes into the cells under the heading of "Calib Date" and you will see what I mean. I need for the formula to test for an entry of of NA and basically use that entry as a TRUE test in the formula.

J Morrow

,

[Bob Phillips]

That conflicts with what you said earlier ... there will be entries of "NA" to indicate a calibration is not required. Now you say it should equate to TRUE, but which true, there are a number.

[J Morrow]

I thank you very much for your time and your help.

I did not think that I contradicted myself but it would appear that I have in my explanation ... sorry about that ... just another example of attempting to put in writing of which is simpler to explain verbally in person.

If I input NA into cell I9, I need a result in cell E9 to be String Check, if I input NA into cell I15; I need a result in cell E15 to be Ready. If I use several of the varying formulas I and other people have suggested, I obtain a result Calib and String Check respectively.

Does that make sense or am I still not communicating effectively?

One such modified formula is:
=IF(F3="Red Tagged","Red Tagged",IF(OR(AND($D$1>G3+365,$D$1>H3+365,$D$1>J3+365),AND($D$1<G3+365,$D$1>H3+365),AND($D$1<G3+365,$D$1>H3+365,$D$1<J3+365)),"PM2",IF(OR(AND($D$1>G3+365,$D$1<H3+365),AND($D$1>G3+365,$D$1<H3+365,OR(I3="NA",$D$1<IF(I3="NA",0,I3)+30)),AND($D$1>G3+365,$D$1>H3+365,$D$1<J3+365),AND($D$1>G3+365,$D$1<H3+365,$D$1>IF(I3="NA",0,I3)+30,$D$1<J3+365)),"PM1",IF(OR(AND($D$1<G3+365,$D$1<H3+365,$D$1>IF(I3="NA",0,I3)+30),AND($D$1<G3+365,$D$1<H3+365,$D$1>IF(I3="NA",0,I3)+30,$D$1<J3+365)),"Calib",IF(AND($D$1<G3+365,$D$1<H3+365,$D$1<IF(I3="NA",0,I3)+30,J3<I3),"String Check",IF(AND($D$1<G3+365,$D$1<H3+365,$D$1<IF(I3="NA",0,I3)+30,J3>=I3),"Ready",""))))))

[Bob Phillips]

I think I see what you mean, but this is awkward.

You have to change each reference of I9 to something like IF(I9="NA",TRUE,IF(original_I9_condition,TRUE)). So the following condition in the PM1 test

$D$1<I9+30

needs to be changed to

IF(I9="NA",TRUE,IF($D$1<I9+30,TRUE))

Trouble is that this then creates too many nested levels, so to get around that you need to split the tests up. So instead of

=IF(cond1,res1,IF(cond2,res2,IF(...

you would use

=IF(cond1,res1,"")&IF(cond2,res2,"")&IF(...

Doing this just for I9 results in an overall formula of

=IF(F9="Red Tagged",F9,"")&
IF(OR(AND($D$1>G9+365,$D$1>H9+365,$D$1>J9+365),AND($D$1<G9+365,$D$1>H9+365),AND($D$1<G9+365,$D$1>H9+365,$D$1<J9+365)),"PM2","")&
IF(OR(AND($D$1>G9+365,$D$1<H9+365),AND($D$1>G9+365,$D$1<H9+365,IF(I9="NA",TRUE,IF($D$1<I9+30,TRUE))),AND($D$1>G9+365,$D$1>H9+365,$D$1<J9+365),AND($D$1>G9+365,$D$1<H9+365,IF(I9="NA",TRUE,IF($D$1>I9+30,TRUE)),$D$1<J9+365)),"PM1","")&
IF(OR(AND($D$1<G9+365,$D$1<H9+365,IF(I9="NA",TRUE,IF($D$1>I9+30,TRUE))),AND($D$1<G9+365,$D$1<H9+365,IF(I9="NA",TRUE,IF($D$1>I9+30,TRUE)),$D$1<J9+365)),"Calib","")&
IF(AND($D$1<G9+365,$D$1<H9+365,IF(I9="NA",TRUE,IF($D$1<I9+30,TRUE)),J9<I9),"String Check","")&
IF(AND($D$1<G9+365,$D$1<H9+365,IF(I9="NA",TRUE,IF($D$1<I9+30,TRUE)),J9>=I9),"Ready","")

Now we have a new problem, it passes 2 of the tests now, so we get a result of CalibString Check.

AT this point, I am not prepared to change all of that formula, it is too time consuming, and the results are ambiguous. You need to take a different approach.

[J Morrow]

I had that "warm and fuzzy feeling"-NOT when I started this project and you would also seem to confirm my fears. My knowledge with Excel is somewhat limited; more than a novice but nowhere close to an expert either. I have zero knowledge with VBA and would not be able to surmise if that could handle this task. Do I need to break this up a little before the final results can be displayed? You have my curiosity peaking when you state that a different approach needs to be addressed. What would you suggest as a possible route?

[Bob Phillips]

codeInteresting question. I would definitely go the UDF route, but that wasn't ore complex than I anticipated. Once I worked out a simple way to handle the NA test, it worked quite well, but be warned, it doesn't necessarily return what you expect, as I mentioned earlier, tests that fail with a date de facto pass with NA.

Public Function StatusCheck(BaseDate, rngRed, rngPM1, rngPM2, rngCalib, rngString)

    If rngRed = "Red Tagged" Then
    
        StatusCheck = "Red Taggged"
    ElseIf (Validate(BaseDate, ">", rngPM1, "+365") And Validate(BaseDate, ">", rngPM2, "+365") And _
            Validate(BaseDate, ">", rngString, "+365")) _
        Or (Validate(BaseDate, "<", rngPM1, "+365") And Validate(BaseDate, ">", rngPM2, "+365")) _
        Or (Validate(BaseDate, "<", rngPM1, "+365") And Validate(BaseDate, ">", rngPM2, "+365") And _
            Validate(BaseDate, "<", rngString, "+365")) Then
           
           StatusCheck = "PM2"
           
    ElseIf (Validate(BaseDate, ">", rngPM1, "+365") And Validate(BaseDate, "<", rngPM2, "+365")) _
        Or (Validate(BaseDate, ">", rngPM1, "+365") And Validate(BaseDate, "<", rngPM2, "+365") And _
            Validate(BaseDate, "<", rngCalib, "+30")) _
        Or (Validate(BaseDate, ">", rngPM1, "+365") And Validate(BaseDate, ">", rngPM2, "+365") And _
            Validate(BaseDate, "<", rngString, "+365")) _
        Or (Validate(BaseDate, ">", rngPM1, "+365") And Validate(BaseDate, "<", rngPM2, "+365") And _
            Validate(BaseDate, ">", rngCalib, "+30") And Validate(BaseDate, "<", rngString, "+365")) Then
           
           StatusCheck = "PM1"
    ElseIf (Validate(BaseDate, "<", rngPM1, "+365") And Validate(BaseDate, "<", rngPM2, "+365") And _
            Validate(BaseDate, ">", rngCalib, "+30")) _
        Or (Validate(BaseDate, "<", rngPM1, "+365") And Validate(BaseDate, "<", rngPM2, "+365") And _
            Validate(BaseDate, ">", rngCalib, "+30") And Validate(BaseDate, "<", rngString, "+365")) Then
        
        StatusCheck = "Calib"
    ElseIf (Validate(BaseDate, "<", rngPM1, "+365") And Validate(BaseDate, "<", rngPM2, "+365") And _
            Validate(BaseDate, "<", rngCalib, "+30") And Validate(rngString, "<", rngCalib, "")) Then
    
           StatusCheck = "String Check"
    ElseIf (Validate(BaseDate, "<", rngPM1, "+365") And Validate(BaseDate, "<", rngPM2, "+365") And _
            Validate(BaseDate, "<", rngCalib, "+30") And Validate(rngString, ">=", rngCalib, "")) Then
    
           StatusCheck = "Ready"
    Else
          
           StatusCheck = ""
    End If
End Function

Private Function Validate(BaseDate, CompString, InputVal, InputAdd) As Boolean
    If InputVal = "NA" Then
    
        Validate = True
    Else
        
        Validate = Application.Evaluate(CLng(BaseDate) & CompString & "(" & CLng(InputVal) & InputAdd & ")")
    End If
End Function

You use like so

=StatusCheck($D$1,F9,G9,H9,I9,J9)