Complex Formulas

The art of programming is the art of keeping things simple. Complex formulas are hardly ever wise to use. Calculations can usually be simplified. You just have to figure out how. PerfectXL points out different types of complex formulas in your spreadsheet and it offers suggestions to improve the complexity.

Complex Formulas – Risks

Complex formulas are risky because they are generally difficult to maintain. Complex formulas are hard to read and might be incomprehensible to other users. Even the original author might forget what he meant to do with a certain calculation.

Complex Formulas :: Risk Detection in Spreadsheets :: PerfectXL

Array Formula

Array functions are too complex to understand for many people, so if a spreadsheet is used by multiple people they are very risky to use. Try to change your formulas in such a way that you don’t need the array formula. For example, try to add an extra column or functions like SUMIFS or MAXIFS to help avoid these complex formulas.

Array Functions :: Complex Formulas :: Risk Detection :: PerfectXL

Conditional Complexity

Conditional complexity occurs when too many nested IF operations exist in one formula. This is risky, because it makes the formula hard to read. Try to split up the formula into multiple steps over multiple cells or reconsider the necessity of some of the conditions.

Conditional Complexity :: Complex Formulas :: Risk Detection :: PerfectXL

Many Operations

Too many operations in one formula makes the formula unreadable, incomprehensible and non-transferable to users other than the original author(s). It is recommended to split up the formula into two or more separate cells.

Many Operations :: Complex Formulas :: Risk Detection :: PerfectXL

References to Many Cell Groups

Avoid complex formulas that mention too many cell groups. The user is likely to forget one reference or another, or to include one too many. In addition, it makes formulas less readable and non-transferable. Either split up the formula or reorganize the cell groups. Where possible, use ranges over individual cell references.

References to Many Cell Groups :: Complex Formulas :: Risk Detection :: PerfectXL

Get PerfectXL Now


Other Types of Risk

Calculation Doubts Structure Issues Range Issues Hidden Info Vulnerable Functions VBA Issues


Relevant Guidelines

Every other week we publish a new Excel Guideline for Professionals, to help you get the best out of your spreadsheets.

About Array Functions