Excel Dynamic Arrays Straight to the Point
September 2018
Introduced on September 24 2018, the new SORT, FILTER, and UNIQUE make everything easier in Excel formulas!
- 57 Pages
- Publisher: Holy Macro! Books
- PDF ISBN: 978-1-61547-265-9
Update - 2nd Edition
Updated version: Excel Dynamic Arrays Straight to the Point 2nd Edition
New for Office 365 customers, one single formula sitting in one single cell can return many results. Those extra results will spill into adjacent cells. This is a major change to the calculation engine in Excel. When you first hear of this feature, you might think it is about SORT, FILTER, UNIQUE, SORTBY, SEQUENCE, RANDARRAY, and SINGLE. But dynamic arrays make every Excel calculation function more powerful. Pass a SEQUENCE to another function and Excel will Lift the function to return many results. In this book, you will learn new terms such as Lifting, Broadcasting, Pairwise Lifting, Array Truncation, and why Implicit Intersection was the culprit in making the old Ctrl+Shift+Enter formulas so hard. Plus, you will never need to press Ctrl+Shift+Enter again.
-
Introduction
- What Will the Headlines Say About Dynamic Array Formulas?
- This Book Is Not the Comprehensive Guide to Dynamic Arrays
- Dynamic Array Formulas and Their Offspring Are Office 365 Exclusive
- How This Book Is Organized
-
Formulas Can Now Spill
- What Happens if A Formula can't spill?
- If Your Formula Points to a Table, the Array Will Expand
- What Is Really Happening Behind the Scenes?
- Using the New Array Reference Notation: E3#
- What About Implicit Intersection?
-
The SORT Function
- A Simple Sort with One Argument
- A Sort Based on Two or More Columns of Results
- A Random Sort Using SORT and RANDARRAY
- What's Left for Ctrl+Shift+Enter?
-
The SORTBY Function
- A Sort by Something That Is Not in the Results
-
The FILTER Function
- Using The FILTER Function With One Condition
- Using FILTER with Multiple Conditions
-
The UNIQUE Function
- Syntax of the UNIQUE Function
- Understanding Unique Versus Distinct
-
Combining Functions
- Nesting Array Functions: SORT and UNIQUE
- Nesting Array Functions: SORT, UNIQUE, and FILTER
-
The SEQUENCE Function
- Generating a Range of Sequential Numbers
- Using SEQUENCE Inside Another Function
-
The RANDARRAY Function
- Generating an Array of Random Numbers with RANDARRAY
- Simulating RANDBETWEEN
- Using RANDARRAY for Modeling and Simulation
-
Why CSE Arrays Were So Hard: Implicit Intersection
- A Quick Glossary
- Legacy Excel Used Arrays Far More Often Than We Realized
- Why CSE Formulas Were So Hard
- Understanding Implicit Intersection
- Breaking Implicit Intersection
- Lifting When a Scalar Is Expected but an Array Is Provided
- Understanding Array Truncation
- Using a Wrapper Function in Legacy Excel
- Preventing Implicit Intersection with Ctrl+Shift+Enter
- From Lifting to Pairwise Lifting
- Broadcasting Makes All Arrays the Same Size
- A Simple Broadcasting Example
- How Do Lifting, Broadcasting, Array Truncation, and Implicit Intersection Affect Dynamic Arrays?
-
Other Functions That Are Now Dynamic Arrays
- Using TODAY and SEQUENCE for a Calendar
- NOW and SEQUENCE
- Generating Sequential Letters with CHAR, SEQUENCE, and TEXTJOIN
- Returning the N Largest Items Using LARGE
- Returning the N Smallest Items Horizontally
- Transposing with a Shorter Formula
- Showing Formulas for a Range with FORMULATEXT
- Creating a Crosstab Report with Three Formulas
- Displaying Numbers as Binary, Octal, or Hex by Using BASE
- Summing the Lengths of Many Cells
- Using a Formula to Convert Text to Columns
- Summing All VLOOKUPS
- Finding the Proper Case of All Names with One Formula
- Replacing a What-If Data Table with One Formula
- Applying Up/Flat/Down Icons by Using the SIGN Function
- Using the Spilled Range Operator to Point to an Array
- Using an Array Reference as Part of a Reference
- Generating a Series of Months
- Forecasting with an Array
- Forecasting 12 Months by 5 Years
- Transposing One Array to Prevent Pairwise Lifting
- Forecasting All Five Years in One Formula
- Combining Array Formulas to Simplify Cube Formulas
- Using Dynamic Arrays for Dependent Validation
- There Will Be Hundreds More Examples
- What Is the VBA Story?
Updates
- Version 1: September 25, 2018
-
Version 2: September 27, 2018. New in this version:
- General copyediting and typo corrections
- "Using the New Array Reference Notation: E3#" on page 8
- "Using FILTER with Multiple Conditions" on page 18
- "Understanding Unique Versus Distinct" on page 19
- "Creating a Crosstab Report with Three Formulas" on page 42
- "Using an Array Reference as Part of a Reference" on page 48
- "Using Dynamic Arrays for Dependent Validation" on page 58
- "What Is the VBA Story?" on page 58
- Version 2a: September 28, 2018. Typo corrections in Index & Table of Contents
- Version 2b: September 29, 2018. More copyediting corrections.
-
Version 2c: November 19, 2018: New in this version:
- Spiller illustration
- Problems with incorrect application of SINGLE on page 9
- Formula for unique combinations of two non-adjacent columns on page 21
