Formula Structure for Performance

how to create best formula for using IF ELSE?

Accepted answers

my 10 cents here..say you calculate something based on a condition and 0 otherwise, you could structure it like

if not <condition> then 0 else <calculation>..by this way you get faster performance (lets say you are expecting 0 on more than 50% of the cells)...

LipChean_Soh

Just to add onto what @ArunManickam mentioned above, you can also consider the following:

1. If you're doing a boolean test, eg If <condition> then TRUE then FALSE; then just write <condition> because it'll give you the same result.

2. If the 'If then else' starts to look big and tough to understand, you can copy it from Anaplan, and paste it in Notepad, so that you can create indentation to help you read/modify more easily, eg

"if condition A then result 1 else if condition B then if condition C then result 2 else result 3 else result 4"

can be converted into the following in Notepad, before you paste it back. Please remember to get rid of the 'Tab' before you process the formula.

"if condition A then

result 1

else if condition B then

if condition C then

result 2

else

result 3

else

result 4"

3. Referring to the same 'if then else' scenario in item 2, or another 'if then else' which is even longer, you can also consider breaking it into multiple line items, eg

Line Item 1 = if condition A then result 1 else if condition B then if condition C then result 2 else result 3 else result 4

can be broken up into

Line item 1 = if condition A then result 1 else Line Item 2

Line Item 2 = if condition B then if condition C then result 2 else result 3 else result 4

4. If you see a lot of 'If then else' that refers to ITEM(), eg

'If item(Product)=Product.A then result 1 else if ITEM(Product)=Product.B then .....

then you'll need to consider using another Anaplan feature, LOOKUP. Please refer to the following for more details on LOOKUP.

https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/LOOKUP.html?_ga=2.142814449.55075348.1551800869-1239638901.1550667215

Thanks,

LipChean

All comments

adisulyadi

IF else on the anaplan ?

ArunManickam

my 10 cents here..say you calculate something based on a condition and 0 otherwise, you could structure it like

if not <condition> then 0 else <calculation>..by this way you get faster performance (lets say you are expecting 0 on more than 50% of the cells)...

LipChean_Soh

Just to add onto what @ArunManickam mentioned above, you can also consider the following:

1. If you're doing a boolean test, eg If <condition> then TRUE then FALSE; then just write <condition> because it'll give you the same result.

2. If the 'If then else' starts to look big and tough to understand, you can copy it from Anaplan, and paste it in Notepad, so that you can create indentation to help you read/modify more easily, eg

"if condition A then result 1 else if condition B then if condition C then result 2 else result 3 else result 4"

can be converted into the following in Notepad, before you paste it back. Please remember to get rid of the 'Tab' before you process the formula.

"if condition A then

result 1

else if condition B then

if condition C then

result 2

else

result 3

else

result 4"

3. Referring to the same 'if then else' scenario in item 2, or another 'if then else' which is even longer, you can also consider breaking it into multiple line items, eg

Line Item 1 = if condition A then result 1 else if condition B then if condition C then result 2 else result 3 else result 4

can be broken up into

Line item 1 = if condition A then result 1 else Line Item 2

Line Item 2 = if condition B then if condition C then result 2 else result 3 else result 4

4. If you see a lot of 'If then else' that refers to ITEM(), eg

'If item(Product)=Product.A then result 1 else if ITEM(Product)=Product.B then .....

then you'll need to consider using another Anaplan feature, LOOKUP. Please refer to the following for more details on LOOKUP.

https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/LOOKUP.html?_ga=2.142814449.55075348.1551800869-1239638901.1550667215

Thanks,

LipChean

Quick Links

Can you help?

Unanswered questions

Workaround for Formula & Closing Balance summary methods together in Polaris
Hello folks, Does anyone know how to work around the Polaris limitation of using the Formula or Ratio summary method alongside the Closing Balance within the same line item? I have a Business Unit list where the total (All BUs) must display the Corporate (1 of 20 leaf items under All BUs total) number of Unique Customers.…
AnaplanXL Difficulties
Has anyone found success using Anaplan XL with Polaris models in a shared drive with or without concurrent users? My team has experienced a variety of errors, login failures, etc. sometimes it works, sometimes it doesn't. We've been recommended to save and edit files locally, which helps but certainly with its limitations…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…