General Principals

Reading Order

In general, formulas should be placed in reading order. This means a formula's precedents should be found to the left (on the same row), above (in the same worksheet) or on prior worksheets.

Worksheet Placement

Where possible, nothing on a worksheet contains anything from subsequent worksheets to the right. 

Exceptions: 

1. Integrity checks messages link to the Integrity Checks worksheet which is usually the last worksheet

2. The table of contents link to subsequent worksheets to the right.

Formula Placement

Where possible, no formula will contain references to:

1. Cells on worksheets to the right

2. Cells in rows below

3. Cells to the right on the same row

NOTE! Corkscrew formulas may appear to have precedents to the right, but those precedents should be in the row above which keeps them in reading order.

Segregate Inputs, Processes and Outputs

In general, Inputs should never be on the same worksheet as model calculations and calculation should never be formatted and used as outputs. Keeping inputs away from calculations prevents inadvertent calculation changes that would invalidate a model. Keeping calculations away from outputs allows us to structure our models efficiently, with flexibility, and provide a single version of the truth for outputs. Having all outputs pull from the same set of calculations keeps all outputs in sync. 

NOTE! Some will want inputs on the same page as outputs so users can play with inputs and see results immediately. This can be achieved using dual monitors and opening two windows for our project. Place the inputs in one monitor and the outputs in another rather than placing inputs in the outputs section.

Exceptions: When someone identifies a reason for an exception, we will post it here.

Literals (aka Constants and Hardcoded Values)

In general, no formulas should contain any numbers. Numbers should be replaced with named constants.

Exceptions: Identity numbers, which include 1 and 0, are allowed when the formula's context makes it obvious what they mean. 

No Cell References

In general, use Named ranges or Structured References in formulas. Names (which include structured references) add context and documentation to our work which is crucial to making our work understandable and maintainable. For more on this subject click Cell References and Names.

Exceptions: When someone identifies a reason for an exception, we will post it here.