Formula Readability

We all know how easily things can get out of control when writing a complex formula. It may provide the correct answer, but will anyone understand it?

A good definition from Copilot:

The clarity, simplicity, and interpretability of a formula, enabling users to quickly understand its purpose, logic, and components without needing extensive explanation.

NHL v3 Download Excel File

Requirements

Given the data on the left, create the output on the right:

use a single formula
all parts horizontally aligned (column M) even if text length changes
include title “NHL 1980-1981 Season Round 1 Playoff Bracket”
copy/paste single formula to reuse solution

Yes, it would be MUCH easier to use five columns and align text as needed…but I wouldn’t get to use the LET function!

Solution 1

=VSTACK("NHL 1980-1981 Season Round 1 Playoff Bracket",
        LET(
        t, B3:B18,  s,B3:B10,
        M,MAX(LEN(s))+1,  N,MAX(LEN(t)),
        arrayI,{1;2;3;4;5;6;7;8},b,17-arrayI,
        arrayI&" "&INDEX(s&REPT(" ",M-LEN(s))&"vs ",arrayI)&
        INDEX(t&REPT(" ",N-LEN(t)),17-arrayI)&" "&IF(LEN(b)=1," "&b,b)))

At first I liked this formula due to it’s short length. When I went back later to change something, I realized it was difficult to audit.

Solution 2

LET function is amazing! It even lets us reduce function names to a single letter:

=VSTACK("NHL 1980-1981 Season Round 1 Playoff Bracket",
LET(
t, B3:B18, s,B3:B10, X,MAX, L,LEN, I,INDEX, R,REPT,
m,X(L(s))+1, n,X(L(t)),
a,{1;2;3;4;5;6;7;8},b,17-a,
a&" "&I(s&R(" ",m-L(s))&"vs ",a)&
I(t&R(" ",n-L(t)),17-a)&" "&IF(L(b)=1," "&b,b)))

Shorter but even more difficult to audit! I like the idea of being kind to my future self when auditing/modifying a formula weeks or months in the future.

Solution 3

I finally settled on this formula. Much longer but kind 🙂

=VSTACK("NHL 1980-1981 Season Round 1 Playoff Bracket",
LET(
TopTxt,B3:B10, EndTxt,B11:B18, RankA,{1;2;3;4;5;6;7;8}, RankB,17-RankA, RankC,RankB-8,

a,N("RankBB pushes rank 9 one additional space to the right"),
RankBB,IF(LEN(RankB)=1," "&RankB,RankB),

b,N("Calc longest text for top & bottom team sets. Pad others with spaces"),
LongTxtA,MAX(LEN(TopTxt)),
LongTxtB,MAX(LEN(EndTxt)),
PadA,REPT(" ",LongTxtA-LEN(TopTxt)),
PadB,REPT(" ",LongTxtB-LEN(EndTxt)+1),

c,N("Concatenate parts together"),
RankA&" "& INDEX(TopTxt&PadA,RankA)
&" vs " & INDEX(EndTxt&PadB,RankC)&RankBB))

Ideas I used in this formula:

longer descriptive variable names for ranges and calculations
ALT + ENTER for carriage returns (imagine without them!)
N function to comment a section of similar items (except 1st row of LET which I think is easy to read)
final formula: concatenates parts together (no additional calcs)
could I have used a single range along with TAKE function?

Recap

We all have different skills and preferences…but I don’t think anyone enjoys auditing mega formulas! Even if you don’t like your colleagues, you should be kind to your future self!

LET function’s ability to define reusable variables reminds me of DAX variables that breaks logic into smaller pieces.

How would you have solved this using a single formula? (leave a comment below)
What have I not mentioned that’s essential to my solution? (non formula related)

Have you ever?

Have you ever used CELL function’s width info_type? Check out sheet ‘easy solution’!

Hmm…I noticed that manually checking a column’s width is not always the same result as CELL function. AI gave me what seems to be a solid answer:

1980-1981 NHL Season and Playoffs

The NHL created playoff pairing based on overall team rank (not division or conference) for the 81 playoffs. It was fair, but my team got clobbered in the first round by the reigning and eventual repeat champions (1980s New York Islanders dynasty). Cub fans and Red Sox fans endured longer before finally winning again….one day it will happen.

About Me

My name is Kevin Lehrbass. I’m a data analyst. My other hobbies include: learning Spanish, playing chess, and drawing.

The site is about Microsoft Excel.