Notes on Excel Dynamic Arrays
Tags: excel
Published: 2022-02-23 14:30:50

## 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](https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531)
- [Calculation differences between dynamic arrays vs CSE arrays](https://support.microsoft.com/en-us/office/dynamic-array-formulas-vs-legacy-cse-array-formulas-ca421f1b-fbb2-4c99-9924-df571bd4f1b4)

### TRANSPOSE

| | A| B| C| D
|---|---|---|---|---|
|1| Apple| 10| | <pre class="highlight postscript"><code>=TRANSPOSE(<span class="s">$A$1:$B$6</span>)</code></pre>
|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| | <pre class="highlight postscript"><code>=UNIQUE(<span class="s">$A$1:$B$6</span>)</code></pre>
|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| | <pre class="highlight postscript"><code>=SORT(UNIQUE(<span class="s">$A$1:$B$6</span>))</code></pre>
|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| | <pre class="highlight postscript"><code>=FILTER(<span class="s">$A$1:$B$6</span>, <span class="s">LEFT($A$1:$B$6, 1)="B"</span>)</code></pre>
|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 | | <pre class="highlight postscript"><code>=<span class="s">$A$1:$B$2</span>*2</code></pre>
|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| | <pre class="highlight postscript"><code>=MMULT(<span class="s">$B$2:$D$4</span>, <span class="s">$G$2:$G$4</span>)</code></pre>
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

```ps
=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 |||<pre class="highlight postscript"><code>=LET(currentColRef, COUNTA(<span class="s">$A$2:$N$2</span>), currentRevenues, INDIRECT(CONCAT("R2C", currentColRef,":R3C", currentColRef), FALSE), currentRevenues - OFFSET(currentRevenues,0,-1))</code></pre>
|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