IF Formula - For combining a combination of criteria

svbhagat

Hello,

I'm hoping I can get some help with using an IF formula to combine a criteria.  

The following three line item formulas are working correctly:

Then I have the following two line items that show what list items should be ignored.

Where I am having trouble is where I try to combine the two criteria in blue above and then still pull the data.

Here is the data:

If the data is a combination of Year 2018 and Company AU 100-517, then it should be ignored otherwise everything else should be included.

Column F and G - Should be pulling data only for 2018-100-500; 2019-100-500; 2019-100-517.  But currently it is only pulling data for 2019-100-517.  How can I update the formula to pick up the other two criterias?

 

Thanks for the community's help in advance.  

JaredDolich

@svbhagat, what a great question.

 

I would suggest all your conditionals be created in system modules. You can learn more by reading about the DISCO methodology.

System modules are single dimensional modules, even time, where you put all your Booleans for use in conditional statements.

There are several advantages to this:

1. you only calculate the Boolean once.

2. you can combine them to create more complex Booleans, like the ones you're trying to create above.

3. the conditionals are more readable, and thus auditable (PLAN methodology)

4. your formulas are more efficient because  you only calculated the Boolean once

5. Booleans are resuable, meaning you can also use them for filtering <- nice Anaplan feature.

 

Lastly, by putting the most likely condition first in your IF statement, you will prevent Anaplan from calculating the remainder of the formula making your calculations even faster.

 

Here's a small example to show you what I mean.

First set up your system modules and add your Booleans.

You'll need one for company and time. In this case, I'll show you time.

Notice the Boolean is calculated once. From now one we'll refer to this instead of calculating it again.

Then in your output or calculation module, you will refer to this Boolean in your conditional.

Remember to always pick the most likely condition first so Anaplan will exit the calculation sooner.

In this case the invalid years are more likely (there are 6 of them and only 5 valid years)

 

And the result is what we wanted - only valid years are caculated.

Now, just repeat this process for companies.