Result formula based in column responses

[SKETCHwade]

Here is a portion of my inspection form:

The header for each section eg Fire Suppression system?
determines wether or not the section is required/shown.

I am trying to write an expression for the Result that will do the following:
For Fire Suppression system
Date within 6 months?
Pressure good?
Visual condition good?

For External Fire Extinguisher
Date within 6 months?
Pressure good?
Visual condition good?

For Internal Fire Extinguisher
Date within 6 months?
Pressure good?
Visual condition good?

Result = Pass

If a section is not shown because it is not required then its result is not relevant to the final Result

I hope this makes sense. Thank you

[Steve]

SKETCHwade:

I hope this makes sense.

Not to me.

[SKETCHwade]

Sorry @Steve

Is there any section in particular that does not make sense?
Or should I try to rewrite this?

[SKETCHwade]

This may help further explain my intentions:

IF([Fire Suppression System?] = Yes,
IF([Fire Suppression Date] > Today()-182,Pass,Fail)
IF([Fire Suppression Pressure] = Good,Pass,Fail)
IF([Fire Suppression Visual Condition] = Good,Pass,Fail)
,Fail)

You can see here the overarching Question is does the [Fire Suppression System?] exist?
If it does not then ignore the section about the fire suppression system.
If it does exist then do its three following columns pass the criteria?

The Result column should display pass if the Fire Suppression System? column is no (It does not exist so should not affect the result for the rest of the form)
Or if the columns meet the criteria. Otherwise the Result column should display Fail.

Note the code I used above for this example does not work the way I want it to. It displays fail if you select No on the initial column and sometimes displays pass if you make some columns fail the criteria.

[Suvrutt_Gurjar]

I believe you may try following

IFS(

[Fire Suppression System?] = FALSE, “Pass”,

AND([Fire Suppression System?] = TRUE, [Fire Suppression Date]>Today()-182, [Fire Suppression Pressure] = “Good”, [Fire Suppression Visual Condition] = “Good”), “Pass”,

AND([Fire Suppression System?] = TRUE, OR([Fire Suppression Date]<=Today()-182, [Fire Suppression Pressure] = “High”, [Fire Suppression Pressure] = “Low”, [Fire Suppression Visual Condition] =“Replace”) ), “Fail”

)

Edit: : I also believe that for all these check conditions, you may wish to have some proper initial values for all these inspection fields in consistent manner for either “Fail” logic or "Pass"logic.

So [Fire Suppression Pressure], [Fire Suppression Visual Condition] etc. may all have initial values that satisfy “Pass” logic or values that satisfy “Fail” logic. This will ensure that the expressions do not fail because of “undefined” values for these fileds.

[SKETCHwade]

Thank you @Suvrutt_Gurjar

This helped me get a lot closer I think.

Here is what I have:

IFS(

[Fire Suppresion System?] = FALSE, “Pass”,

  AND([Fire Suppresion System?] = TRUE,
  [Fire Suppresion Date]>Today()-182,
  [Fire Suppresion Pressure] = “Good”,
  [Fire Suppresion Visual Condition] = “Good”),
  “Pass”,

  AND([Fire Suppresion System?] = TRUE,
  OR([Fire Suppresion Date]	<=Today()-182,
  [Fire Suppresion Pressure] = “High”,
  [Fire Suppresion Pressure] = “Low”,
  [Fire Suppresion Visual Condition] =“Replace”)
  ),“Fail”,

[External Fire Extinguisher?] = FALSE, “Pass”,

AND([External Fire Extinguisher?] = TRUE,
  [External Fire Extinguisher Date]>Today()-182,
  [External Fire Extinguisher Pressure] = “Good”,
  [External Fire Extinguisher Visual Condition] = “Good”),
  “Pass”,

AND([External Fire Extinguisher?] = TRUE,
  OR([External Fire Extinguisher Date]	<=Today()-182,
  [External Fire Extinguisher Pressure] = “High”,
  [External Fire Extinguisher Pressure] = “Low”,
  [External Fire Extinguisher Visual Condition] =“Replace”)
  ),“Fail”,

[Internal Fire Extinguisher?] = FALSE, “Pass”,

  AND([Internal Fire Extinguisher?] = TRUE,
  [Internal Fire Extinguisher Date]>Today()-182,
  [Internal Fire Extinguisher Pressure] = “Good”,
  [Internal Fire Extinguisher Visual Condition] = “Good”),
  “Pass”,

AND([Internal Fire Extinguisher?] = TRUE,
  OR([Internal Fire Extinguisher Date]	<=Today()-182,
  [Internal Fire Extinguisher Pressure] = “High”,
  [Internal Fire Extinguisher Pressure] = “Low”,
  [Internal Fire Extinguisher Visual Condition] =“Replace”)
  ),“Fail”

)

It looks pretty long and frightening but Its really just an extended version of what you presented.

My issue is that due to the way IFS work getting a pass by selecting no on the column [Fire Suppression System?] means the result is essentially locked as pass even though the internal fire extinguisher may have failed for example.

Any ideas?

[Suvrutt_Gurjar · Accepted solution]

Hi @SKETCHwade,

You have extended the same logic over other subcategories. That is good however i believe it needs tweaking as below, if I have understood your requirement correctly.

Please try following expression. It assumes that if each of the subcategories, namely [Fire Suppresion System?], [External Fire Extinguisher?] and [Internal Fire Extinguisher?]and their questions result in to respective subcategory being pass, then overall result is “Pass” . If any one of those subcategories fail , the overall result is “Fail”

IF(AND(
IFS(

[Fire Suppresion System?] = FALSE, “Pass”,

AND([Fire Suppresion System?] = TRUE,
[Fire Suppresion Date]>Today()-182,
[Fire Suppresion Pressure] = “Good”,
[Fire Suppresion Visual Condition] = “Good”),
“Pass”,

AND([Fire Suppresion System?] = TRUE,
OR([Fire Suppresion Date] <=Today()-182,
[Fire Suppresion Pressure] = “High”,
[Fire Suppresion Pressure] = “Low”,
[Fire Suppresion Visual Condition] =“Replace”)
),“Fail”)=“Pass”,

IFS(

[External Fire Extinguisher?] = FALSE, “Pass”,

AND([External Fire Extinguisher?] = TRUE,
[External Fire Extinguisher Date]>Today()-182,
[External Fire Extinguisher Pressure] = “Good”,
[External Fire Extinguisher Visual Condition] = “Good”),
“Pass”,

AND([External Fire Extinguisher?] = TRUE,
OR([External Fire Extinguisher Date] <=Today()-182,
[External Fire Extinguisher Pressure] = “High”,
[External Fire Extinguisher Pressure] = “Low”,
[External Fire Extinguisher Visual Condition] =“Replace”)
),“Fail”)=“Pass”,

IFS(

[Internal Fire Extinguisher?] = FALSE, “Pass”,

AND([Internal Fire Extinguisher?] = TRUE,
[Internal Fire Extinguisher Date]>Today()-182,
[Internal Fire Extinguisher Pressure] = “Good”,
[Internal Fire Extinguisher Visual Condition] = “Good”),
“Pass”,

AND([Internal Fire Extinguisher?] = TRUE,
OR([Internal Fire Extinguisher Date] <=Today()-182,
[Internal Fire Extinguisher Pressure] = “High”,
[Internal Fire Extinguisher Pressure] = “Low”,
[Internal Fire Extinguisher Visual Condition] =“Replace”)
),“Fail”
)=“Pass”),“Pass”,“Fail”)

[SKETCHwade]

Thank you @Suvrutt_Gurjar

That is perfect!!

Your explanation for my intention was also spot on:

Please try following expression. It assumes that if each of the subcategories, namely [Fire Suppresion System?], [External Fire Extinguisher?] and [Internal Fire Extinguisher?]and their questions result in to respective subcategory being pass, then overall result is “Pass” . If any one of those subcategories fail , the overall result is “Fail”

I really appreciate it @Suvrutt_Gurjar !

[Suvrutt_Gurjar]

Thank you for the update @SKETCHwade. You are welcome and good to know it helps.