Can you afford to not keep up with Excel?
Excel Dynamic Arrays Straight
to the Point
Author: Bill Jelen, MrExcel
Shop with Confidence
✔ 100% Secure Shopping
✔ No Complex Software or Subscriptions
✔ Secure Digital Delivery with E-Junkie
✔ No Expensive Licenses
✔ 6.9MB PDF Download
✔ 2nd Edition PDF e-Book
✔ PDF ISBN: 978-1-61547-265-9
✔ 80 Pages
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:
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
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
- 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
Does the Refund Apply to Partner Products?
The Terms and Conditions of Sale for Partner Products including refunds is in accordance with the Partners Terms and Conditions. Refer to their website.
While Digital Documents Direct makes every attempt to provided detailed, comprehensive information, you are responsible for reviewing, checking and confirming the information provided is suitable for your use.
Is this complicated Software or Subscriptions?
No. This is not complicated software where you need an ongoing subscription or annual fee. It is a one off payment.
Do I need a Paypal account to purchase?
No, you do not need a Paypal account to purchase our products. You can pay with Paypal or use your credit card.
How will my information be used?
Your information will not be used whatsoever without your permission. In other words, your email address will not be sold or spammed.
Will I get on a spam list?
No. We may add you to our newsletter list. However you can ‘opt out’ at any time. Above all, you will not be spammed.
Will I be the victim of identity theft?
No. Paypal and e-Junkie provide a 100% secure system to perform the transaction.
Will the purchase arrive on time?
Yes. As soon as you have paid for your purchase, you will receive a download link via email. However, please remember to also check your ‘Junk Email’ as if you have not received your link immediately, the email could be in your ‘Junk Email Inbox’.
Should you not receive the download link, please contact us immediately and we will get back to you as soon as we can. Please note, the time zone of your country may effect our response time, however we will respond as soon as we can. Our business is located in Australia.
AEST – Australian Eastern Standard Time.
Will I actually get what I ordered?
Yes. We promise to deliver to you and stand by this promise. Further to this, Paypal guarantees your transaction so you can shop with full confidence.
Will unexpected fine print charges be added to my order without my knowledge?
No. There are no further charges. No monthly subscriptions either. You have the right to use the document to your desires within the terms and conditions of the Partner website. Refer to their website for more information.
Will anyone respond if I have problems after buying?
Yes. However, there may be a delay in our response. Depending on your country timezone, we will respond as soon as we can. Please note, we endeavor to respond to your requests within the shortest possible time.
I did not receive a download link after paying
Please do not panic. e-Junkie are both very reliable document delivery systems and it is unlikely this will occur. However, if it does, we will email you the download link as soon as we can.
The website briefly redirected through e-Junkie. Is this safe?
Yes it is safe. When you click on the Proceed to Pay button, you will be redirected briefly through e-Junkie.com to Paypal.com depending on how you choose to pay. Partner products are delivered electronically so you receive them immediately – 24 Hours a day, 7 days a week.
Copyright © 2022
All rights reserved. No part of this website or information may be reproduced or used in any manner without written permission of the copyright owner.