Excel BI has some fun challenges. This one has several solutions but would the average Excel user understand them?

AI Formula Help

These solutions were created by advanced Excel users, but more and more, people will use AI to write formulas instead of asking a professional for help. So…

How will beginning and intermediate Excel users know if their AI created formula is correct?

I’m not saying it’s AI’s fault. AI does amazing things but if the question isn’t phrased properly the solution won’t be correct. And, AI does make mistakes. Now back to Excel BI’s challenge.

Back to the Challenge

Note: I replaced original letters in columns B & E with real class subjects.

Challenge: a student takes a subject (column B) and receives a mark (column C). That mark is compared to the passing mark (column F) to determine a pass or fail grade.

With such a small set of data, we can compare Marks with Passing Marks and easily confirm all answers:

• Mike passed Algebra, Chem, and Geography but failed Biology and Economics
• Anne passed Geography and Economics but failed Algebra, Biology, Chem
• Jug passed all subjects

Real world data sets can have 10000s of rows. We have to be sure that the formula is correct as we can’t manually check every row.

Pick a Solution!

In selecting a solution, think of this:

Could you explain the solution to a basic/intermediate user when handing off the model to them?

I realize that this doesn’t always happen, but it should!
Review the solutions in the Linkedin post comments.

My Favorite Solution

Below, the solution I like. There’s a tricky part but it’s shorter than most (all?) other solutions.

I reformatted Abbott’s formula to make it easier to read:

=DROP(
      PIVOTBY(
              A2:A16,
              IF(C2:C16>=VLOOKUP(B2:B16,E2:F6,2)," Pass","fail"),
              B2:B16,
              ARRAYTOTEXT,,0,,0
      ),
      1
 )

Abbott’s Solution Explained

Let’s start by focusing only on function PIVOTBY:

=PIVOTBY(
         A2:A16,
         IF(C2:C16>=VLOOKUP(B2:B16,E2:F6,2)," Pass","fail"),
         B2:B16,
         ARRAYTOTEXT,,0,,0
 )

Let’s examine PIVOTBY function arguments:

• row_fields: unique text values (students) from A2:A16
  • Mike
  • Anne
  • Jug
• col_fields: the tricky and brilliant part!
  • IF(C2:C16>=VLOOKUP(B2:B16,E2:F6,2),” Pass”,”fail”)
  • normally, unique text values are spread horizontally across the top of a pivot. In this case, IF function compares all marks with their passing mark number and assigns each one to belong on the left column (” Pass”) or right column (“fail”)
• values: B2:B16 values. Not numbers to sum or average but text values!
• function: ARRAYTOTEXT aggregates (concatenates) text values together in a single cell
  • cell H12 seen below: has ‘Pass Subjects’ Geography and Economics concatenated together for student Anne
• other function arguments are skipped or have a value of 0

For each unique column A Student, column B Subjects are concatenated together into column ” Pass” or column “Fail” based on each Mark status.
DROP function removes the top row that has text ” Pass” or “Fail”.

One thing I couldn’t initially figure out:

Why does column ” Pass” start with a space?

Pass column needs to be displayed on the left but the F in Fail comes before the P in Pass so a space is included to force Pass to the left!

Include Pivot Column Headers

Let’s add column headers to make the solution look exactly like this:

Here’s the formula:

=LET(
col_h, {"Student", "Pass Subjects", "Fail Subjects"},
formula,
       DROP(
            PIVOTBY(
                    A2:A16,
                    IF(C2:C16>=VLOOKUP(B2:B16,E2:F6,2)," Pass","fail"),
                    B2:B16,
                    ARRAYTOTEXT,,0,,0
            ),
       1
       ),
final, VSTACK(col_h,formula),
final)

LET function allows us to break logic down into steps via variables:

• col_h the three text values in the array constant are the column headers
• formula the formula that we reviewed in this post
• final VSTACK stacks variable col_h on top of formula result

About Me

My name is Kevin Lehrbass. I’m a long term Microsoft Excel user and fan. I love it! Even though I’ve worked with SQL, Power BI, etc., Excel is still my favorite. It’s everywhere and Microsoft keeps adding features and functions. I can’t wait to see what they do next!

Learning an incredibly useful yet complex function like Excel’s LAMBDA requires the right mix of theory and examples (and a large coffee!).

LAMBDA Theory

You can use LAMBDA function with named ranges (ExcelJet), but in this post I’ll review an examples using LAMBDA inside another function (ExcelisFun).
Dave Brun’s post and Mike Girvin’s video give you enough initial theory.

Download the Excel file for this post and follow along:

Example1: fill in the blanks

Requirement & Solution

This is from MeganathanElumalai. The requirements are clear and the formula is short. Tricky part is understanding what happens inside TOCOL.

Solution Explanation

=MAP(B2:D23,LAMBDA(x,TAKE(TOCOL(B2:x,3,1),-1)))

Theory says MAP function passes each individual value from B2:D23 (left to right, top to bottom) into LAMBDA where a calculation will occur.

Inside LAMBDA, these two parts are easy to understand:

• parameter: LAMBDA gives MAP’s B2:D23 range the name x
• calculation: TAKE function extracts the last value out of function TOCOL

What is TOCOL(B2:x,3,1) doing?
First, a straight forward use of TOCOL. Cell A6 formula results spill down:

Instead of vertically stacking each full column in the range left to right, C2:x creates expanding ranges starting in the top left of range B2:D23.

So, how can we see all the ranges to fully understand what’s happening?

We can’t. We can select the entire formula and press F9 key but that shows us the output we already see in range F2:H23. I did find a way to peer inside TOCOL!

Remember: TOCOL removes blanks from each range and vertically stacks remaining values by column. TAKE function keeps the last (bottom) value.

Let’s see how cell G8 gets value “Feb” using TAKE(TOCOL(B2:x,3,1),-1)

• TOCOL’s current B2:x range is highlighted green (B2:C8)
• second argument uses 3 to ignore (remove) blanks
• third argument 1 stacks remaining values left to right (column J)
• TAKE function uses -1 to keep the last value “Feb”

To see each range, click the spin button form control (bottom left).
Below, column B’s green range provides cell F15 with “Q2”.

Understanding the ranges TOCOL(B2:x,3,1) creates is the key!

Example2: multiple ranges

A simple example to show that LAMBDA can work with multiple ranges.

Yes, =SUMPRODUCT(A2:A11,B2:B11) or an array gives the same answer but LAMBDA with the initial function (in this case MAP) has tons of potential!

Example3: moving average

There are many ways to write a moving average formula.

Simple but volatile?

=MAP(B2:B51,LAMBDA(data,AVERAGE(OFFSET(data,,,4))))

Efficient but complex?

=MAP(B2:B51,LAMBDA(d,LET(r,ROW(d),last,ROW(B51),AVERAGE(INDEX(B:B,r): INDEX(B:B,MIN(r+3,last))))))

Or a little of both?

=MAP(B2:.B100000,LAMBDA(d,AVERAGE(d:INDEX(B:B,ROW(d)+3))))

Creating sample data

I’ve created a lot of sample data over the years. Sample data often relies on volatile functions like RANDBETWEEN and RAND. I use this technique to keep the formula for future reference when I want to refresh the sample yet avoid volatility.

Lambda Function Review

Lambda function is incredible! It can do some amazing things that previously were not possible. However, a word of caution. I have also seen some unnecessarily complex solutions that could be solved in an easier way without Lambda function.

About Me

I’m a long term Microsoft Excel user and fan. I love it! Even though I’ve worked with SQL, Power BI, etc., Excel is still my favorite. It’s everywhere and Microsoft keeps adding features and functions. I can’t wait to see what they do next!

Regex is incredibly powerful and versatile. I’ll share some resources, a challenge and several techniques that are helping me learn.

What is Regex?

Regex (aka Regular Expressions) finds, extracts, and validates text using patterns instead of exact words. It evolved from a mathematical concept into a practical tool for text processing. 

Why learn Regex?

Q: Why should we spend time learning regex syntax?
A: Even basic regex knowledge can save hours of work.

Regex in Microsoft Excel 365

Regex was introduced in Excel in 2024. Three functions use regex syntax:

• REGEXEXTRACT
• REGEXREPLACE
• REGEXTEST

Regex Intro Resources

Start with regex theory from Mynda Treacy, Leila Gharani, Dave Burns, and Sumit Bansal:

• MyOnlineTrainingHub: excel-regex-functions
• MyOnlineTrainingHub: regex-regular-expressions-excel
• MyOnlineTrainingHub: Excel Regex Cheatsheet
• Xelplus: Regex in Excel
• Xelplus: Introducing REGEX Excel functions
• Exceljet: REGEXEXTRACT function
• TrumpExcel: New Excel REGEX Functions are INSANELY Powerful

Don’t memorize too much theory. Solve problems as soon as possible!

Like learning a language: speak ASAP! Learn basic grammar, speak, repeat. Some regex theory, practice, more theory, more practice. Let’s review a challenge!

Regex LinkedIn Challenge

Challenge Definition

From Mohammad Abou Daher: split column A text into three columns: ID, Capital, State Code.

Complex Solution

Mohammad’s solution is brilliant but the average Excel user wouldn’t understand this:

=REDUCE({"ID","Capital","State Code"}, A3:A12,
     LAMBDA(x,y,
           VSTACK(x,
                 LET(
                    a, TEXTJOIN("",, IFERROR(MID(y, SEQUENCE(LEN(y)),1)*1,"")),
                    b, CONCAT(REGEXEXTRACT(y,"[A-Z]{2}")),
                    c, SUBSTITUTE(SUBSTITUTE(y,a,""),b,""),
                    HSTACK(a,c,b)
                 )
           )
     )
 )

LAMBDA function is fascinating but complex. Let’s solve it an easier way that most users will understand.

Less Complex Solutions

First, I’ll solve it using three separate formulas.
Then, I’ll combine all three formulas into a single formula.

Three Separate Formulas

Formula 1: cell D3

=REGEXEXTRACT(A3:A12,”^[0-9]+”)

• text: range A3:A12 causes formula to spill results to row 12 (or use A3 and drag formula down)
• pattern: “^[0-9]+”
  • ^ the pattern must start at the beginning of a cell
  • [0-9] digits from zero to nine
  • + take all continuous digits from the start of the pattern
• [return_mode] and [case_sensitivity] are optional and not needed

Formula 2: cell F3

=REGEXEXTRACT(A3:A12,”[A-Z]{2}”)

• text: range A3:A12 causes formula to spill results to row 12 (or use A3 and drag formula down)
• pattern: “[A-Z]{2}”
  • [A-Z] upper case text from A to Z
  • {2} take ONLY the first two letters
• [return_mode] and [case_sensitivity] are optional and not needed

Formula 3: cell E3

=TEXTAFTER(A3:A12,D3#&F3#)

• text: range A3:A12 causes cell E3 formula to spill results to row 12
• delimiter: D3#&F3# (cell E3: take whatever comes after “1042TX” in cell A3)
  • concatenate cell D3 value with cell F3 value
  • # symbol: concatenate spill values below cells D3 and F3
• other function arguments are optional and not needed

These formulas solve the challenge and are easier to understand and explain to others. Why is this important? Because spreadsheets are passed around to different departments and companies.

Single Formula

=LET(
Titles,     {"ID","Capitol","State Code"},
Text,       A3:A12,
ID,         REGEXEXTRACT(Text,"^[0-9]+"),
State_Code, REGEXEXTRACT(Text,"[A-Z]{2}"),
Capital,    TEXTAFTER(Text, ID&State_Code),
Stack,      HSTACK(ID, Capital, State_Code),
Final,      VSTACK(Titles, Stack),
Final)

It looks complex doesn’t it? I’ll explain it from the beginning.

Single Formula Step 1

Cell I3 LET function allows us to define all 3 formulas inside 1 formula.
# spill character isn’t used. HSTACK horizontally stacks them.

Single Formula Step 2

In step 1, I repeat range A3:A12 in each formula. Now, variable Text is used in each formula. I can reuse the formula and change the range once.

Single Formula Step 3

Lastly, I created variable Titles for the column headers. Variable Final uses VSTACK to place column headers on top of the columns.

Now that we’ve solved a regex problem, what’s next? Let’s use AI to study regex examples and then experiment with sample text!

Tools to Continue Learning

Modern tools make it SO much easier to ramp up our regex skills!

Use AI to create regex examples

I used Perplexity AI on my phone with this prompt:

Create a list of 1000 regex sample solutions using Microsoft Excel regex functions

Perplexity created groups. Below, the first 15 examples in group ‘Basic pattern matching’

1. Basic pattern matching (1–60)

1. =REGEXEXTRACT(A2,"[0-9]+") (extract first number sequence)​
2. =REGEXEXTRACT(A2,"[A-Za-z]+") (extract first contiguous letters)​
3. =REGEXEXTRACT(A2,"^[0-9]+") (extract leading digits)
4. =REGEXEXTRACT(A2,"[0-9]+$") (extract trailing digits)
5. =REGEXEXTRACT(A2,"^\s*[A-Za-z]+") (extract first word, ignoring leading spaces)
6. =REGEXEXTRACT(A2,"\b\w+\b") (extract first word using word boundary)
7. =REGEXEXTRACT(A2,"\b\d{4}\b") (extract 4‑digit number, e.g., year)
8. =REGEXEXTRACT(A2,"\b[A-Fa-f0-9]{6}\b") (extract 6‑digit hex)
9. =REGEXEXTRACT(A2,"[A-Z]{3}") (extract first 3 capital letters)
10. =REGEXEXTRACT(A2,"[A-Za-z]{2,}") (extract first word with 2+ letters)
11. =REGEXEXTRACT(A2,"[aeiouAEIOU]") (extract first vowel)
12. =REGEXEXTRACT(A2,"[^0-9]+") (extract first non‑numeric run)
13. =REGEXEXTRACT(A2,"[^A-Za-z]+") (extract first non‑letter run)
14. =REGEXEXTRACT(A2,".{5}") (extract first 5 characters)
15. =REGEXEXTRACT(A2,"^.{3}") (extract first 3 characters)

I study the examples and occasionally lookup syntax definition. It’s great!

regexr.com

I’m using regexr.com to practice. I love it!
There’s a sample expression, text and explanation. ([A-Z])/w+ highlights words with an upper case letter. Top left area has helpful features to explore.

Modify the expression and observe the results. Here are some I made:

• ([e-f])]w+
• ([c-h)\W+
• (^[E-Z])\w+
• ([a-z^])\w+

Ensure ‘Explain’ is selected below. The explanations are really helpful!

Next, I logged in via google account and started exploring with a new set of sample text.
a[^aeiou] highlights a lower case a followed by anything except lower case aeiou.

In Excel, I used the expression inside function REGEXEXTRACT:
Cell B6 formula counts the occurrences of any lower case letters followed by anything except aeiou.

regex101.com

regex101.com is a similar site where you can practice regex expressions. \b\w{4}b highlights words with a length of 4 characters.

In Excel, this formula extracts all four letter words:

=REGEXEXTRACT(A2,”\b\w{4}\b”,1)

I kept experimenting late last night past 1am!
I’ll now learn more theory in a different area like numbers, email, url, dates etc. and use these tools to practice with a new set of sample data!

Conclusion

Knowing even a few basic regex patterns can save hours of work!
Complex expressions require work to build but remember how we solved things before regex! Slowly and painfully using basic string manipulation functions.

About Me

Me and a very friendly cat named Ox. My cousin loves animals and volunteers at a cat shelter. I wish I could have adopted Ox but my dogs Cali and Fenton would have protested!

I love learning patterns in Excel, Chess, Spanish grammar, Kakuro etc. It’s fun. I know it’s an over used phrase but I really am I life long learner. I’m not happy if I’m not learning something.

When modeling in Excel should we only use traditional functions or combine traditional and dynamic array functions? Let’s use segmentation as an example.

What is segmenting data?

Data segmentation: dividing data into meaningful subgroups.
Below, two columns of data from Hablando de Excel.
Age: range B7:B106
Value: range C7:C106

Requirement: Sum values per age group (group increment is ten).

Now we know WHAT segmentation is, but HOW should we calculate it?

Traditional or Dynamic Array Functions?

Traditional Functions: multiple formula solution using traditional basic functions (IF, MIN, MAX, SUMIFS). Each cell produces an independent result. Some formulas are manually dragged down.

Dynamic Array Functions: single formula spills results to multiple cells by combining traditional functions (IF, MIN, MAX, SUMIFS) and dynamic array functions (LET, GROUPBY, VSTACK, HSTACK).

Hablando de Excel combines 2 traditional functions (SUM, ROUNDDOWN) with 2 dynamic array functions (LET, GROUPBY).

(English available in settings)

Let’s compare solutions to understand pros and cons.

Excel file: Albert’s original file with my formulas added.

Traditional Functions: solutions 1 and 2 both use 17 formulas that must be manually dragged down if the number of segments increases. Increment amount can be changed without touching the formulas. Boundaries are visible to decrease formula complexity.

Dynamic Array Functions: solutions 3 and 4 use only 1 formula! You’ll NEVER need to drag the formula down further as it automatically spills down/across as needed. MAGIC! But, it’s complex and difficult to modify for most people.

Let’s compare solution 3 and 4 in more detail:

Solution 3 is from Hablando de Excel. Thank you Albert! His formula:

=LET(vi, ROUNDDOWN(B8:B107,-1), GROUPBY(vi&"-"&vi+9,C8:C107,SUM,0,0))

It only uses two dynamic arrays: LET and GROUPBY. It could be explained to people with intermediate knowledge. In many cases it’s the perfect formula.

Solution 4 is mine. Why is my formula so complex???

=LET(
age,   B8:B107,                  values, C8:C107,           size,10,
low,   MIN(FLOOR(age,size)),     high, MAX(FLOOR(age,size)),
rows,  (high-low)/size+1,
group, SEQUENCE(rows,1,low,size),
calc,  SUMIFS(values,age,">="&group,age,"<"&group+size),
title, HSTACK("Age","Value"),    data, HSTACK(group&"-"&group+size,calc),
final, VSTACK(title,data),
final)

Empty bins:
I found something that might mean nothing to some and everything to others.

• all solutions have bins starting with the lowest rounded down value (21 rounded to 20)
• each upper bin is 10 above
• if we change the data (check box in cell U2) solution 3 skips bin 30-39 as there’s no data (this may or may not matter to you)

It may be obvious why a bin is missing. Imagine outliers well above the rest of the data. Why include many bins with no data?
In other cases, it would be important to include empty bins and investigate why they are empty.

Increment amount:
Solution 3 and 4 include the increment amount inside the formula. This could easily be changed to include it in a cell like in solutions 1 and 2.

Can you think of a shorter solution that includes empty bins?
Or, a formula that includes a switch to include/hide empty bins?

If segmenting is new to you, scroll down to the Segmenting Guidelines section.

Let’s not forget my original question: When modeling in Excel should we only use traditional functions or combine traditional and dynamic array functions?

Below, formulas are highlighted in grey. Solutions 1 & 2 use 17 simple formulas but it’s a lot to audit and maintain.
Imagine a workbook with many complex sheets. I’d rather audit one complex formula that uses dynamic array functions than dozens of formulas that contribute to the answer and require modifications.

Not convinced? Read this post that reviews Carl Seidman’s course: Advanced Excel for Financial Planning and Analyst (FP&A).

Segmenting Guidelines

M.E.C.E. explanation from Google AI search:

The M.E.C.E. (Mutually Exclusive, Collectively Exhaustive) principle is a data segmentation framework that ensures categories for a dataset or problem-solving structure are distinct and complete. 

The two core rules for M.E.C.E. segmentation are:

• Mutually Exclusive (ME): Each data record or item fits into one and only one category. There is no overlap among groups, which prevents double-counting and ambiguity.
• Collectively Exhaustive (CE): All possible items, scenarios, or options are covered by the established categories. The sum of all categories equals the whole, ensuring no relevant information is overlooked. 

Application in Data Segmentation

When segmenting data using the M.E.C.E. principle, the goal is to create a structure that is clear, comprehensive, and efficient for analysis and decision-making. 

• Ensures clarity: Each item has a single, specific place, which avoids confusion and makes analysis straightforward.
• Guarantees comprehensiveness: The process forces you to consider all possibilities, reducing the risk of missing important factors or segments.
• Improves efficiency: By avoiding overlap, you prevent the duplication of work when different teams or processes are assigned to different segments. 

Examples of M.E.C.E. Segmentation

Financial Metrics: Breaking down profit into “Revenue” and “Costs” is M.E.C.E. because all components of profit are included, and no value can be both a revenue and a cost simultaneously.

Age Brackets: Segmenting a population by age groups like “0-17”, “18-65”, and “66+” is M.E.C.E. because everyone falls into one category, and no one falls into two simultaneously.

Geographic Regions: Dividing sales data by continents (e.g., North America, South America, Europe, Asia, etc.) ensures that all global sales are covered and none are double-counted.

What’s in a Name?

A summary from ChatGPT to compare segmenting terms.

🧮 Statistical & Analytical Terms

There’s no single universal term that covers grouping, segmenting, and binning perfectly across all contexts, but here’s how professionals typically refer to this concept depending on how broad you want to be:

✅ Most Common General Term

“Data segmentation”

This is the most broadly understood and widely used umbrella term for dividing data into meaningful groups — whether that’s by bins, categories, or clusters.
It’s common in analytics, business intelligence, statistics, and machine learning.

“The model uses segmented input features to capture nonlinearity.”

Used when: You’re dividing a dataset into parts for analysis, modeling, or interpretation.

Examples: “We performed data segmentation based on customer age and spending.”

About Albert Cid Cañigueral

Albert is a Microsoft Excel MVP from La Sénia, Catalonia, Spain.
He has a treasure chest of over 800 YouTube videos!

Follow Albert here:
Linkedin linkedin.com/in/hablandodeexcel
Facebook facebook.com/AlbertCidC
Instagram instagram.com/albert_cidc
tik tok tiktok.com/@albert.cid.hd.excel

About Me

My name is Kevin Lehrbass. I’m a Data Analyst and I live in Milton, Ontario, Canada. Fenton and Cali are my amazing dogs! My other hobbies include playing chess and learning Spanish.
That’s why I enjoyed Albert’s video so much. I just watched his most recent video: RETO EXCEL: ¿Eres capaz de NORMALIZAR DATOS CON FÓRMULAS? Another great video! I can understand very well but I need to learn Excel function names in Spanish. I can guess some of them, but not all of them.

I’ve seen some amazing videos showcasing the potential of dynamic array functions but seeing them used in a model is even more impressive!

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.

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

Update: use “comment1” etc as variable names (no need for N function). Thanks to Mahmoud Bani Asadi!

=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,

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

comment2,"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),

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

And this is why we share ideas! We all learn and we all win!

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.

I used this post by Meganathan Elumalai to explore the transition from traditional formulas to spill formulas (formula results spill into multiple cells).

Meganathan’s requirements are clear:

Meganathan used a table in column B for the source items. In this post I’ll use a non table range (my Excel file includes both).

Formula1:

Cell D5 formula, in the picture above, is also listed below:

=IF(ISERR(SEARCH(D$4,$B5)),"",D$4)

Will it spill? No. This formula won’t spill into other cells.

Why doesn’t it spill?

SEARCH function’s default use is to look for a single cell value within another single cell like we have here: SEARCH(D$4,$B5)

Cell D5 Formula Description

Look for cell D4 search word (Apple) within cell B5. If doing this causes an error (Apple isn’t within cell B5), then display a blank (“”). If it is found then display cell D4 search word (Apple).

This is the traditional method to solve this. The formula is then dragged down and right as needed. There’s nothing wrong with this but now we have more options: a formula can spill answers into other cells without being dragged or copied!

Before looking at Formula2, I’ll explain two things in my Excel file:

Conditional Formatting

Cells with formulas are highlighted in green (see picture below) if the check box is checked:

• Formula1 in cell D5 was dragged down to row 10 and across to column I so all cells are green
• Formula2 in cell D17 automatically spills down to row 22! I dragged cell D17 formula to the right to cell I17. Each formula spills down to row 22 (without being dragged down!)

Search Words (fruit)

• In range D4 to I4, search words (fruit) are typed in
• In cell D16, an array constant formula spills fruit names to the right until cell I16. The only formula in range D16 to I16 is in cell D16

Formula2:

Cell D17 formula, in the picture above, is also listed below:

=IF(ISERR(SEARCH(D$16,$B$5:$B$10)),"",D$16)

Will it spill? Yes. Row 17 formulas spill down, not right (cell D17 formula is dragged right to cell I17).

How does it spill down?

As we saw earlier, SEARCH is typically used to look for a single cell value inside of another single cell (exception: array formulas) like this: SEARCH(D$4,$B5)

But, Formula2 SEARCH(D$16,$B$5:$B$10) searches for Apple in a range of cells: $B$5 to $B$10 creating multiple answers. Cell D17 formula spills down to cell B22 as range $B$5:$B$10 has 6 cells.

• D17 formula: cell D16 search word Apple is in cell B5 so display Apple
• D17 formula spills to cell D18: cell D16 search word Apple isn’t in cell B6 so blank
• D17 formula spills to cell D19: cell D16 search word Apple is in cell B7 so display Apple
• D17 formula spills to cell D20: cell D16 search word Apple isn’t in cell B8 so blank
• D17 formula spills to cell D21: cell D16 search word Apple is in cell B9 so display Apple
• D17 formula spills to cell D22: cell D16 search word Apple isn’t in cell B10 so blank

Cell D17 formula is then dragged right until cell I17.

Remember: only cells with green background have a formula. Other cells receive spill formula results or have typed in text values.

Formula3:

The formula in cell D29 is listed below:

=IF(ISERR(SEARCH(D28#,$B$5:$B$10)),"",D28#)

Will it spill? Yes! Cell D29 formula spills down AND to the right!

How does it spill down AND right?

Three factors allow cell D29 formula to spill down AND right:

1. cell D28 has an array constant formula that spills search words right to cell I28
2. cell D29 has # after both D28 cell references (D28#) telling it to spill right as wide as the spill range of the array constant above
3. cell D29 SEARCH function has a multi cell within_text argument range ($B$5:$B$10) causing it to spill down to row 34

Let’s validate this by changing cell D29 formula.

Remove the ability to spill right by changing D29 formula to this:

=IF(ISERR(SEARCH(D28,$B$5:$B$10)),"",D28)

Remove the ability to spill down by changing D29 formula to this:

=IF(ISERR(SEARCH(D28#,$B5)),"",D28#)

Formula4:

The formula in cell N4 is listed below:

=LET(
header,{"Apple","Banana","Grape","Kiwi","Orange","Pear"},
formula,IF(ISERR(SEARCH(header,$B$5:$B$10)),"",header),
VSTACK(header,formula))

Will it spill? Yes! Cell N4 formula spills search words and then spills answers down AND to the right.

How does it spill search words AND formula results?

• LET function lets us define a name for a calculation (or array constant)
• the name header has this formula: {“Apple”,”Banana”,”Grape”,”Kiwi”,”Orange”,”Pear”}
• the name formula has this formula: IF(ISERR(SEARCH(header,$B$5:$B$10)),””,header)
• VSTACK function vertically stacks these two spill pieces together

Formula5:

The formula in cell N16 is listed below:

=LET(
header,Fruit,
formula,IF(ISERR(SEARCH(header,$B$5:$B$10)),"",header),
VSTACK(header,formula))

Will it spill? Yes! Cell N16 formula spills just like Formula4 except that name header refers to a named range called Fruit that has an array constant.

Formula6:

The formula in cell N29 is listed below:

=DROP(REDUCE(0,B5:B10,LAMBDA(a,i,VSTACK(a,MAP(D4:I4,LAMBDA(x,IF(ISNUMBER(FIND(x,i)),x,"")))))),1)

Will it spill?

Yes…but I can’t explain this formula yet. It was created by Excel MVP Rick Rothstein. It combines a lot of dynamic array functions so I definitely want to study it!

About Me

My name is Kevin Lehrbass. I’m a data analyst.

Formulas that spill results into other cells are very powerful and have changed the way we build Excel models. They require special attention as they can also cause issues if not designed and tested carefully.

Blendtec company founder Tom Dickson experiments to see what his blender can chop up. Microsoft Excel formulas are like that too…we have to study and experiment to see what they can do!