Formulas: Are there long formulas, such as complex IF THEN ELSE statements?


When is this a problem?

If you can't explain the purpose of a formula in one sentence, the formula is too long.


Why is this a problem?

Long formulas, such as complex IF THEN ELSE statements, are difficult to understand, audit, and maintain. Complex formulas use valuable processing time and require the engine to work harder than necessary.


How to correct:

Simplify complex IF THEN ELSE statements and put the most common condition first. Break up formulas into multiple line items. If your formula includes more than 10 IF THEN calculations, think about using a LOOKUP. In some situations, you can also consider using a line item subset.


More information:

Planual 2.02-17

Planual 2.02-18

Formula Structure for Performance

Thinking Through Your Formulas

Line Item Subsets Demystified

Manipulating Data with Line Item Subsets

Decreasing the Length of Your Formulas

Model Optimization Checklist