Dynamic Arrays Straight to the Point

Can you afford to not keep up with Excel?

Excel Dynamic Arrays Straight
to the Point
PDF E-Book

Author: Bill Jelen, MrExcel

Click here to read more and purchase

USD 4.99
Shop with Confidence
Partner Product

Description

Fifteen months after Dynamic Arrays debuted for Office Insiders, the functions are being released to General Availability. This second edition of the book has been updated with new examples: see how Dynamic Arrays make XLOOKUP better.

The chapter on the logic behind arrays has been expanded. A new chapter asks Microsoft for the functions that are truly needed to round out dynamic arrays: TOTALS, HTOTALS, VSTACK, SLICES, UNPIVOT, and more. See how you could use the FastExcel V4 SpeedTools to add these functions today.

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, SORTBYUNIQUE, SEQUENCE, and RANDARRAY.

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. Learn how to use # and @ in your formulas.

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.

Table of Contents

• 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
  • Download the Sample Files
• 1 – Getting Started
  • 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?
• 2 – The SORT Function
  • A Simple Sort with One Argument
  • Sorting with a Single Argument
  • A Sort Based on Two or More Columns of Results
  • Sort by Column
  • A Random Sort Using SORT and RANDARRAY
  • What’s Left for Ctrl+Shift+Enter?
• 3 – The SORTBY Function
  • A Sort by Something That Is Not in the Results
  • Performing a Multi-Column Sort without Array Constants
• 4 – The FILTER Function
  • Using The FILTER Function With One Condition
  • Using FILTER with Multiple Conditions
• 5 – The UNIQUE Function
  • Syntax of the UNIQUE Function
  • Understanding Unique Versus Distinct
• 6 – Combining Functions
  • Nesting Array Functions: SORT and UNIQUE
  • Nesting Array Functions: SORT, UNIQUE, and FILTER
• 7 – The SEQUENCE Function
  • Generating a Range of Sequential Numbers
  • Using SEQUENCE Inside Another Function
• 8 – The RANDARRAY Function
  • Generating an Array of Random Numbers with RANDARRAY
  • Using RANDARRAY for Modeling and Simulation
• 9 – Why CSE Arrays Were So Hard: Implicit Intersection
  • Why CSE Formulas Were So Hard
  • A Quick Glossary
  • Legacy Excel Used Arrays Far More Often Than We Realized
  • 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?
  • Why Did Excel Add an Implicit Intersection Operator?
  • Answers to the Questions at the Start of this Chapter
• 10- XLOOKUP and Dynamic Arrays
  • XLOOKUP is First New Function After Dynamic Arrays
  • Returning 12 Columns of VLOOKUP
  • Returning 12 Months with MATCH and Several INDEX
  • Returning 12 Columns of XLOOKUP without Dynamic Arrays
  • Returning 12 Months with one XLOOKUP
  • Dynamic Arrays Bug: Copying the Formula
  • A Two-Way Lookup with INDEX & MATCH
  • A Two-Way Lookup with XLOOKUP
• 11 – 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
  • Wrapping Data To Columns using INDEX
  • Combining Array Formulas to Simplify Cube Formulas
  • Using Dynamic Arrays for Dependent Validation
  • There Will Be Hundreds More Examples
  • How Will VBA Handle Dynamic Arrays?
• 12 – Building on Dynamic Arrays
  • Adding Totals with FastExcel SpeedTools
  • Excluding Certain Columns from Being Totaled
  • Adding Totals Above and Left of the Array
  • Unpivoting with a Formula
  • Returning Slices from An Array
  • Stacking Multiple Arrays or Ranges
  • Comparing Two Lists
  • A Better OR Function
  • How to Encourage the Calc Team to Adopt These Functions

Frequently Asked Questions – Partner Products