Notes on Excel Dynamic Arrays

Overview

Dynamic arrays are formulas that can return arrays of variable size. Dynamic arrays have been made available in Excel 2021 (and hence Excel 365).

Prior to dynamic arrays, there were static arrays, aka CSE arrays ( because you have to press Ctrl+Shift+Enter).

One of the benefits of dynamic arrays is that it is calculated at runtime.

References:

• Dynamic arrays and spill behavior
• Calculation differences between dynamic arrays vs CSE arrays

TRANSPOSE

	A	B	C	D
1	Apple	10		=TRANSPOSE($A$1:$B$6)
2	Bottom	20
3	Jeans	30
4	Jeans	40
5	Boots	50
6	Fur	60

Result:

	D	E	F	G	H	I
1	Apple	Bottom	Jeans	Jeans	Boots	Fur
2	10	20	30	40	50	60

UNIQUE

	A	B	C
1	Apple		=UNIQUE($A$1:$B$6)
2	Bottom
3	Jeans
4	Jeans
5	Boots
6	Fur

Result:

	C
1	Apple
2	Bottom
3	Jeans
4	Boots
5	Fur

SORT

	A	B	C
1	Apple		=SORT(UNIQUE($A$1:$B$6))
2	Bottom
3	Jeans
4	Jeans
5	Boots
6	Fur

Result:

	C
1	Apple
2	Boots
3	Bottom
4	Fur
5	Jeans

FILTER

	A	B	C
1	Apple		=FILTER($A$1:$B$6, LEFT($A$1:$B$6, 1)="B")
2	Bottom
3	Jeans
4	Jeans
5	Boots
6	Fur

Result:

	C
1	Bottom
2	Boots

Matrix Multiplication

Scalar Multiplication

	A	B	C	D
1	1	2		=$A$1:$B$2*2
2	3	4

Result:

	D	E
1	2	4
2	6	8

Matrix x Matrix

	A	B	C	D	E	F	G	H	I
1		AUD	GBP	EUR		FX Rates			Into USD
2	Product 1	1,000	0	0		AUD	0.7		=MMULT($B$2:$D$4, $G$2:$G$4)
3	Product 2	0	1,000	0		GBP	1.3
4	Product 3	0	0	1,000		EUR	1.1

Result:

	I
1	Into USD
2	700
3	1300
4	1100

Case Study

Grouping non-contagious cells with FILTER

	A	B	C	D	E	F	G	H
1	Student	English Grade	Units	Science Grade	Units	Math Grade	Units	Count of >95
2	Adam	85	1.0	97	1.8	95	1.0	???

Problem: Count all of Adam's grade which are at least 95.

Solution

=LET(grades, FILTER($B2:$G2, {1,0,1,0,1,0}), greater, FILTER(grades, (grades>=95)), COUNT(greater))

Breakdown

LET
name1: grades
value   = FILTER($B2:$G2, {1,0,1,0,1,0})
        = {85, 97, 95}

name2: greater
value   = FILTER(grades, (grades>=95))
        = FILTER({85, 97, 95}, (grades>=95))
        = FILTER({85, 97, 95}, {FALSE, TRUE, TRUE})
        = {97, 95}

calculation: COUNT(LEN(greater))
= COUNT({97, 95})
= 2

Calculating differences between columns

	A	B	C	D	...	P
1		2022-02-17	2022-02-18			Day on Day
2	Sales	925,000	1,050,000			=LET(currentColRef, COUNTA($A$2:$N$2), currentRevenues, INDIRECT(CONCAT("R2C", currentColRef,":R3C", currentColRef), FALSE), currentRevenues - OFFSET(currentRevenues,0,-1))
3	Other Revenues	-	5,000

Breakdown

LET
currentColRef = number of non-empty cells in $A$2:$N$2
        = 3

currentRevenues = values at cells in currentColRef
        = {1 050 000, 50 000}

calculation:
        currentRevenues - column right before currentRevenues
        = {1 050 000, 50 000} - {925 000, 0}
        = {125 000, 5 000}

Result

	P
1	Day on Day
2	125,000
3	5,000