Beyond Excel's (BXL's) Formula Templates

For the most part, TBM uses just a handful of formulas templates. These templates are shown below. There are also only a handful of functions used which include: IF(), INDEX(), ROW(),  and SUMIFS(). 

Why are TBM's formulas so long?

The short answer is, to improve understandability and flexibility. These two aspects are far more important than brevity when working with anything other than the simplest of projects. 

Understandability 

In complex projects we may need to refer back to sections built some time ago, and even by someone else. Anyone who has reviewed anyone else's project will say it takes a significant effort to understand other people's work, and the level of effort grows exponentially with complexity. Thus, TBM makes every effort to make projects understandable through standardization, solid structure, labels, comments, and names.

NOTE! In this discussion, the word name is synonymous with structured reference.

Myth: Long formulas are complex

The length of a formula is not an indicator of its complexity. For example: =B1-A1 is shorter than =Revenue-Expenses but these two formulas have identical complexity scores (3).

Myth: Long formulas are harder to understand

The opposite is true when length is due to names versus cell references. For example: =B1-A1 is shorter but we have no idea what it means. On the other hand  =Revenue-Expenses is longer but we understand just by looking at it, this is a basic profit formula.

Myth: Long formulas are more likely to have errors.

Again, the opposite is true when length is due to names versus cell references. For example, when we type =B1-A1 Excel gladly accepts it even if we meant to type C1 instead of B1 and the result may look right. On the other hand, if we type   =Rev3nue-Expenses Excel will alert us with a #NAME? error. 

Myth: Long formulas take longer to type.

Not really, because as we type a name, Excel starts offering names that match what we type from which we can select.

The truth is names make formulas longer AND easier to understand. And easier-to-understand is key to maintaining complex projects. 

Flexibility

Complex projects can take time to create and sometimes persist after creation for years. As time passes, the likelihood that changes will be needed increases. And anyone who has worked more than a handful of projects, even their own, know that we usually do not get halfway through a project before we realize we need to add something. If our projects are not structured properly, or our formulas rely on cell addresses when cells must be relocated, changes can take a lot of time. For these reasons, TBM does not rely on where information is. Instead TBM references data by the table that contains it, and if needed, finds the data by key. Thus, TBM models can grow, and in some cases, without ANY modification to formulas. 

When we include table names, formulas get longer; but, the tradeoff in terms of flexibility and understandability is well worth it. The same holds true for finding data by key instead of just referencing information by cell address.