FAST 3.03-01 Do not write a formula longer than your thumb
Also known as “the rule of your thumb”, referencing the formula in the editing pane, a formula longer than your thumb likely means that it should be broken into more than one step.
FAST 3.03-02 No formula should take more than 24 seconds to explain
Also known as “the rule of seconds”. Understanding and explaining a formula should be a short exercise; if not, break the logic into smaller calculation steps.
FAST 3.03-03 Do not write multi-line formulas
Even if “the rule of your thumb” may be at times bent or broken, under no circumstances should a formula break past a single line. Any such formula is almost certainly too complex, impractical to review, and suffers from the added annoyance (in Excel 2003 and before) that it masks the column letters on the sheet, further hampering model review. Such a formula will certainly break “the rule of seconds”.
FAST 3.03-04 Use a limited set of Excel functions
Very often, by simplifying calculations and breaking them down into calculation block based steps, complex functions are not needed. For specific rules, see Excel Functions.
FAST 3.03-05 Use flags to limit use of IF function
There is nothing wrong with a (simple) use of IF function, as simple statements can generally be read intuitively as simple English. However, it shouldn’t be over-used or used intensively. Circumstances where = IF( TRUE, x, 0), where TRUE is evaluated on some question of time, should be replaced with x * Flag, except where circularity is being deliberately protected. As well = IF (FALSE, x, 0) can be replaced by = x * (1 – Flag).
FAST 3.03-06 Use INDEX (or even CHOOSE) over IF to pick values
For more information on Excel functions, refer to section 4.01: Excel Functions.
FAST 3.03-07 Never use nested IFs
Nested IFs are a common cause of reduced transparency in models. They take a long time for users to decode and understand. They are prone to error as there are multiple combination of logical states that are infrequently properly tested by modellers.
Multiplication by flags can often be used to evaluate elements of the logic and breaking the logical steps into separate blocks aids comprehension later.
FAST 3.03-08 Do not use Excel Names
FAST-3.03-08.1 except for external links
FAST-3.03-08.2 except for references for macros
FAST-3.03-08.3 except were non-local precedent references are warranted
The Standard’s approach to specific Excel features are explored in more depth in Chapter 4: Excel Features Used in Modelling, page 39, and Names are discussed in section 4.03: Excel Names, page 42.
Consensus position being developed for subsequent draft.
FAST 3.03-09 Do not construct array formulas
Do not use functions that require array constructions, nor form array-variants with standard functions. The resulting complexity is not warranted.
FAST-3.03-09.1 except when Excel’s Data Table feature is being used
FAST-3.03-09.2 except when calculation cannot be achieved without arrays
FAST-3.03-09.3 except when the logic bloat required to avoid arrays creates a solution that is more difficult to review than the array alternative
The Standard’s approach to specific Excel features are explored in more depth in Chapter 4: Excel Features Used in Modelling.
FAST 3.03-10 Do not use a space as an intersection operator
Consensus position being developed for subsequent draft.
FAST 3.03-11 Beware circularity or #ERRORs protected on inactive branch of IF function
Under some designs, it may be required that an IF statement be used to ‘protect’ circularity that would occur on the inactive branch, ‘FALSE side of the IF conditional. An example of this is the circumstance where the construction = x * Flag is circular, but = IF( Flag = 1, x, 0) is not, i.e. where the time period during which Flag = 0 cause the circularities.
This feature of Excel (not found in Lotus) should not be used casually. When it is used, it should be marked and commented. It leaves the model in a position where it may become circular via a simple input change.
Excel ‘hash errors’, e.g. #REF, #VALUE, #DIV/0, etc., do not manifest themselves if they sit on the FALSE-side branch, even if present in the function itself. Hence, IF( Flag = 1, x, #REF) will not manifest a problem in any or all cells where Flag = 1. Model audit software will often not detect this problem either.
