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.
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.
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.
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.
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.