Results 1 to 8 of 8

Thread: Embedded IF AND OR Formula

  1. #1

    Embedded IF AND OR Formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    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","")))))))

  3. #3
    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

    ,

  4. #4
    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.

  5. #5
    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",""))))))

  6. #6
    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

    Code:
    =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.

  7. #7
    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?

  8. #8
    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.

    Code:
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •