When modeling in Excel should we only use traditional functions or combine traditional and dynamic array functions? Let’s use segmentation as an example.
Read more: Segmenting Data in Microsoft ExcelWhat 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
| Concept | Description | Related Terms |
|---|---|---|
| Grouping | Combining data points based on shared attributes (e.g., group by region or product). | aggregating, categorizing, summarizing |
| Binning | Dividing continuous data into intervals or “bins.” | discretizing, bucketing, interval grouping, quantization |
| Segmenting | Dividing data (often customer data) into meaningful subgroups based on features. | clustering, stratifying, partitioning, cohorting |
| Stratification | Dividing data into layers (strata) for analysis (used in sampling). | stratified sampling, subgrouping |
| Cohorting | Grouping entities (like users) by shared time-based or behavioral traits. | temporal grouping, life-cycle segmentation |
| Classification | Assigning data points to predefined categories (used in ML). | labeling, tagging |
| Clustering | Finding natural groupings in data without predefined labels. | unsupervised segmentation, grouping by similarity |
| Discretization | Turning continuous features into categorical ones. | binning, quantization |
| Bucketing | Another term for binning, often used in databases or ETL. | data bucketing, hashing buckets |
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.
