Solved

Using IF, and AND and OR formula

Forum|Forum|2 years ago
July 6, 2023
9 replies
41 views

ashm
New Participant

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!

Best answer by Sho

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"
    )
  )
)

Alicemartine3
New Participant
Forum|Forum|2 years ago
July 6, 2023

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
Author
New Participant
Forum|Forum|2 years ago
July 6, 2023

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'
    )
  )
)

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"
    )
  )
)

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!

+21

Sho
Inspiring
Answer
Forum|Forum|2 years ago
July 6, 2023

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
Author
New Participant
Forum|Forum|2 years ago
July 6, 2023

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)"

)

+21

Sho
Inspiring
Forum|Forum|2 years ago
July 6, 2023

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.

+25

Alexey_Gusev
Brainy
Forum|Forum|2 years ago
July 6, 2023

thanks!

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
Author
New Participant
Forum|Forum|2 years ago
July 6, 2023

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.

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

ashm
Author
New Participant
Forum|Forum|2 years ago
July 6, 2023

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")
)

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 😞

+25

Alexey_Gusev
Brainy
Forum|Forum|2 years ago
July 7, 2023

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 😞

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

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded