Using IF, and AND and OR formula

[ashm]

Hi I'm trying to use a nested formula to calculate a risk matrix if an entry has different conditions: 

F({ Platform product data sources (from Submissions)} =Platform Product/Data, OR ({Subproccessor (from Submissions)}=Subprocessor), ‘CRITICAL’,

IF ({ Platform product data sources (from Submissions)} =HRIS (People data), "MEDIUM",

IF({Platform product data sources (from Submissions)} =Commercial and Financial platforms/data, 'HIGH','low’)))

But I'm getting a formula error, I'd appreciate any help on where I'm going wrong! Thanks!

[Alicemartine3]

You are encountering a formula error in your nested IF,AND and OR Formula.

IF(
  OR(
    { Platform product data sources (from Submissions)} = Platform Product/Data,
    {Subproccessor (from Submissions)} = Subprocessor
  ),
  'CRITICAL',
  IF(
    { Platform product data sources (from Submissions)} = HRIS (People data),
    'MEDIUM',
    IF(
      {Platform product data sources (from Submissions)} = Commercial and Financial platforms/data,
      'HIGH',
      'low'
    )
  )
)

Based on the formula you provided, it appears that you have some typographical errors in the text strings. Specifically, the words 'CRITICAL', 'MEDIUM', and 'low' should be enclosed in double quotation marks (" ") instead of single quotation marks (' ').

Here's the corrected formula:

IF(
  OR(
    { Platform product data sources (from Submissions)} = "Platform Product/Data",
    {Subproccessor (from Submissions)} = "Subprocessor"
  ),
  "CRITICAL",
  IF(
    { Platform product data sources (from Submissions)} = "HRIS (People data)",
    "MEDIUM",
    IF(
      {Platform product data sources (from Submissions)} = "Commercial and Financial platforms/data",
      "HIGH",
      "low"
    )
  )
)

Please note that the corrected formula assumes that the field names and values you are referencing in your formula are accurate and exist in your Airtable base. Make sure that the field names and values are correctly entered.

[ashm]

Unfortunately this doesn't seem to be working. I am wondering if the problem is that I'm referencing fields from a lookup field? Do I have to create an array/flatten? 

the corrected fields are as follows: 

IF( OR( { Vault Platform product data sources (from Submissions)} = “Vault Platform Product/Data", {Subproccessor (from Submissions)} = “Product Subprocessor" ), "CRITICAL", IF( { Vault Platform product data sources (from Submissions)} = "HRIS (People data)", "MEDIUM", IF( {Vault Platform product data sources (from Submissions)} = "Commercial and Financial platforms/data", "HIGH", "low" ) ) )

thanks! 

[Sho · Accepted solution]

Is it a typo?

Fixed “ to "

IF(
  OR(
    { Vault Platform product data sources (from Submissions)} = "Vault Platform Product/Data",
    {Subproccessor (from Submissions)} = "Product Subprocessor"
  ),
  "CRITICAL",
  IF( { Vault Platform product data sources (from Submissions)} = "HRIS (People data)",
    "MEDIUM",
    IF( {Vault Platform product data sources (from Submissions)} = "Commercial and Financial platforms/data",
      "HIGH",
      "low"
    )
  )
)

[ashm]

Thanks @Sho I then went to build on this formula as follows, but I am not getting any valid results for the logical arguments to return "medium.." or "high" criteria, only Critical, and Low (the last argument). could you please help? Am I missing/using an extra OR/ parenthesis? 

IF(

OR(

{Vault Platform product data sources (from Submissions)} = "Vault Platform Product/Data",

{Subproccessor (from Submissions)} = "Product Subprocessor", {Subproccessor (from Submissions)} = "Vault Subprocessor", {Data Classification (from Submissions)} = "Customer Confidential (intake or reshub data - personal data, Vault code)"

),

"Critical (outage/incident can impact customers/ PII data)",

IF(

OR(

{Vault Platform product data sources (from Submissions)} = "HRIS (People data)",

{Data Classification (from Submissions)} = "Internal (Vault Policies, procedures, etc.)"),

"Medium (outage/incident can impact vaulties/services but not customers)",

IF(

OR(

{Vault Platform product data sources (from Submissions)} = "Commercial and Financial platforms/data", {Data Classification (from Submissions)} = "Confidential (financial information, Vaulties personal data, site analytics etc.) "),

"High (outage/incident can cause moderate operational damage/no PII)",

"Low (outage/incident has minimal impact on Vaulties and customers)"

)

)

)

[Sho]

I think the syntax is fine

Note that string evaluation with equals in an if statement will result in FALSE if there is a difference of one character or space.

If there is any swing in notation, you may want to consider the FIND() or REGEX_MATCH() functions.

[Alexey_Gusev]

Hi,
- instead of {Field} use CONCATENATE({Field}) for lookups to convert array to string
- simplify your formula:

IF(CONCATENATE({Subproccessor (from Submissions)}) = "Subprocessor", 'CRITICAL',
  SWITCH(CONCATENATE({ Vault Platform product data sources (from Submissions)}),
  “Vault Platform Product/Data","CRITICAL",
  "HRIS (People data)", "MEDIUM",
  "Commercial and Financial platforms/data", "HIGH",
  "low")
)

[ashm]

Would you recommend I use Find/RegEX Match function? How do you recommend the order re nesting this? Thanks! 

[ashm]

This unfortunately is not working for me. How does the concatenate and Switch functions work in nested formulas?

it seems the argument for subprocessor to be idetnified as critical is working well, but not the others 😞 

[Alexey_Gusev]

Try from simple
IF( { Vault Platform product data sources (from Submissions)} = "HRIS (People data)",'Y','N')
and if it not Y where it should be Y, find why
If formula gives error, it might be due to field type, use concatenate
If formula gives 'N' (in the record where it should be 'Y') - check extra spaces etc..