Multi factor IF formula not working

[GusIbar]

Hi, hope everyone is safe and healthy!

Im creating a flow to determine if we should charge for some of our departments work or not. I have two joined inputs for Filled requests and Cancelled requests.

The output of this WF should be a simple table with the last column stating, item by item (1 item per row), if the item is Chargeable or Not Chargeable.

My issue is with the last IF statement I have, it results on a couple of false positives. I have gone through many iterations/variations of this formula and I can't seem to make it work. Can you spot my mistake? Let me know if you need more info.

I've tried ELSEIF instead of OR.

Everything in orange font is a calculated field that I ran previous to this last step.

IF [True Cancel] = "Yes"
OR [Mass Cancel] = "Yes"
OR [Found in Filled?] = "Yes"
OR [Opened FY] != 2018 OR 2019 OR 2020 (this field is an Integer)
OR [Country] = "Brazil" AND [Opened FY] = 2018
OR [Country] = "Brazil" AND [Opened FY] = 2019 AND [Charge Quarter] = "Q1"
OR [Company] = "Company1" AND [Opened FY] = 2018
OR [Company] = "Company1" AND [Opened FY] = 2019 AND [Charge Quarter] = "Q1"
OR [Region]="EMEA" AND [Worker-Type Conversion]="Type1"
OR [Region]="EMEA" AND [Worker-Type Conversion]="Type2"
OR [Days Open (Cancelled)] > 365 (this field is an Integer)
OR [Days between Cancel-Offer] < 15 (this field is an Integer)
OR [Offer within X days?] <= 180 (this field is an Integer)
THEN "No"
ELSE "Yes"
ENDIF

What ends up happening with this formula is that some results that don't meet all of the criteria end up as a Chargeable. For example, an item labeled as "Yes" for True Cancel or Mass Cancel would come back as Chargeable, when it shouldn't. The strange part is that some are caught by the formula but not all and after 20 hours of dedicated work is driving me insane.

Any help is appreciated!

Thank you!

[grossal]

Hi @GusIbar,

You definitely need two more conditions here 

OR [Opened FY] != 2018 OR 2019 OR 2020

It should be: OR [Opened FY] != 2018 OR [Opened FY] != 2019 [Opened FY] != 2020

My two cents on the whole formula:

1) Consider about splitting it up into a couple formulas. It will help to understand it later. You don't need more tools for this. You can define multiple formulas in one tool.

 

2) Use (). Sometimes it helps to use () to help the tools to get the right order of OR/AND.

Overall, I would really split it up, no matter if you use a couple formula tools in the grid or you just one with many formulas, but I wouldn't use such a long expression.

Best

Alex

[echuong1]

You need to specify the field being used in the formula for EACH piece. Meaning, the statement that is looking at Opened FY would need the field name in it three separate times. See below for an example:

OR [Opened FY] != 2018 OR [Opened FY] !=  2019 OR [Opened FY] != 2020 (this field is an Integer)

In addition, if you are using multiple criteria for the same set, you would need to nest the criteria in parenthesis. For example, I assume that if Country = Brazil and Opened FY is 2018, you want it to return "No." You would need to nest this in parenthesis. Without them, it's looking at each piece separately. See below for how I believe you want your formula to read:

IF [True Cancel] = "Yes"
OR [Mass Cancel] = "Yes"
OR [Found in Filled?] = "Yes"
OR [Opened FY] != 2018 OR [Opened FY] !=  2019 OR [Opened FY] != 2020
OR ([Country] = "Brazil" AND [Opened FY] = 2018)
OR ([Country] = "Brazil" AND [Opened FY] = 2019 AND [Charge Quarter] = "Q1")
OR ([Company] = "Company1" AND [Opened FY] = 2018)
OR ([Company] = "Company1" AND [Opened FY] = 2019 AND [Charge Quarter] = "Q1")
OR ([Region]="EMEA" AND [Worker-Type Conversion]="Type1")
OR ([Region]="EMEA" AND [Worker-Type Conversion]="Type2")
OR [Days Open (Cancelled)] > 365
OR [Days between Cancel-Offer] < 15
OR [Offer within X days?] <= 180
THEN "No"
ELSE "Yes"
ENDIF

[GusIbar · Accepted solution]

Thank you all for your inputs, everyone had the right idea on using parenthesis however the suggestions on how I managed Opened FY variable did not seem to make any impact to the results. I changed that part of formula to Opened FY < 2018 and that fixed it.

Thanks again!