Marvin

Posted on

# 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:

### 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
2 Adam 85 1.0 97 1.8 95 1.0 ???

Solution

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

Breakdown

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

name2: greater
= 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