Ctrl+Shift+Enter Mastering Excel Array Formulas E-Book

MrExcel's Holy Macro! Books

• Herausgeber: Packt Publishing
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 495

Veröffentlichungsjahr: 2024

Beschreibung

Excel users often encounter limitations with standard formulas, but the Ctrl+Shift+Enter technique changes everything. This book is your gateway to mastering Excel array formulas, revealing their potential to solve complex problems effortlessly. You'll start with the basics, understand the fundamental concepts of array formulas, and gradually progress to advanced applications, including mathematical operations, comparative calculations, and dynamic ranges.
Each chapter is crafted to build your confidence and expertise. From performing array operations that manipulate large datasets efficiently to utilizing advanced functions like SUMPRODUCT and AGGREGATE, you will learn how to apply these tools to real-world scenarios. The guide also covers the creation of dynamic ranges with INDEX and OFFSET, ensuring your formulas remain flexible and powerful even as your data changes.
By the end of the book, you'll not only understand the theoretical aspects of array formulas but also possess the practical skills to implement them effectively. Whether you're creating complex financial models, conducting detailed data analysis, or automating routine tasks, this guide equips you with the knowledge to transform your Excel capabilities and achieve more with less effort.

Leseprobe

Ctrl+Shift+Enter

Mike Girvin

Holy Macro! Books

PO Box 541731

Merritt Island FL 32953

Ctrl+Shift+Enter: Mastering Excel Array Formulas

© 2013 & 2016 Holy Macro! Books and Mike Girvin

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without permission from the publisher. Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an "as is" basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

Author: Mike Excelisfun Girvin

Editor: Kitty Wilson

Layout: Tyler Nash & Bill Jelen

Published by: Holy Macro! Books, PO Box 541731 Merritt Island, FL 32953, USA

Printed in USA

First printing: July 2013. Reprinted with updates July 2016

Tech Editor: Bob Umlas

Cover Design: Shannon Mattiza 6’4 Design

Indexing: Nellie J. Liwam

Distributed by: Independent Publishers Group, Chicago, IL

ISBN: 978-1-61547-007-5 (print)

ISBN: 978-1-61547-109-6 (mobi)

ISBN: 978-1-61547-209-3 (pdf)

ISBN: 978-1-61547-329-8 (epub)

Library of Congress Control Number: 2013938519

Contents

Ctrl+Shift+Enter: Mastering Excel Array Formulas

Dedications

About the Author

Acknowledgements

Introduction

Why in the world would you read a book about array formulas?

Why are array formulas important tools to have in your Excel toolkit?

Why would Mike excelisfun Girvin write a book about array formulas?

About the Book

Files That Accompany the Book

The Ctrl+Shift+Enter: Mastering Excel ArrayFormulas DVD

Who This Book Is For

Special Formatting of Cells with Formulas, Labels, and Raw Data

Remembering the Old Conditional Sum Wizard

Chapter 1: Formula Basics

Excel Files

What This Book Assumes

Excel’s Golden Rule

Formula Elements

Math Operators

Comparative Operators

Types of Formulas

How Formulas Calculate: Order of Precedence in Excel

Number Formatting Is a Façade

Default Alignment for Data in Excel

Chapter Summary

Chapter 2: Introduction to Array Formulas

Excel Files

What Is an Array?

The Types of Arrays in Excel

What Is An Array Formula?

Performing an Operation on an Array of Items Rather Than on Single Items

First Example: Single-Cell Array Formula or Helper Column?

Using the "Evaluate Formula Element" Trick to Show the Resultant Array

Entering an Array Formula into a Cell

SUMPRODUCT Array Operations

Comparing SUMPRODUCT and SUM for Array Calculations

Using a Helper Column or a Single-Cell Array Formula

Advantages and Disadvantages of Array Formulas

Calculation Time for Array Formulas and Large Data Sets

Chapter Summary

Chapter 3: Math Array Operations

Excel Files

Array Operations

Math Operations

A Formula to Calculate Total Net Cost for a Group of Products

Calculating Net Cost from Four Series Discounts in Four Separate Cells

Creating Your Own Array Formulas

How You Can Structure Array Operation with an Operator

Chapter Summary

Chapter 4: Comparative Array Operations and Aggregate Calculations with One or More Conditions

Excel Files

Comparative Operations

Aggregate Calculations Based on One or More Conditions

Using an IF Function to Select Items in An Array Based on one condition

Understanding Database Functions

Should You Use the DMIN Function or the MIN/IF Array Formula?

PivotTables

Should You Use a PivotTable or an Array Formula?

Using the IF Function to Select Items in an Array Based on Multiple Criteria

MAX Calculation with Two Criteria, Using Two IF Functions and Two Comparative Operators

MIN Calculation with the NOT Condition and the Equating Condition

The AGGREGATE Function Array Argument

The IF Function Ctrl+Shift+Enter Trump Rule

Should You Use AGGREGATE and Boolean Conditional Calculation or MIN/IF/IF?

Standard Deviation IF

Filtering Values Inside a Formula

Comparing Aggregate Calculations with Criteria

Timing the Formulas in This Chapter

Chapter Summary

Chapter 5: Join Array Operations

Excel Files

Join Operation

Two Lookup Values for Creating a Cross-Tabulated Report

Using the DGET Function for a Multiple-Criteria Lookup

Using a Helper Column to Speed Up Formula Calculation Time for Two Value Lookup

Using a PivotTable for Two-Value Lookup

Sorting by Two Columns and Using an Approximate Match

LOOKUP Function Arguments: Array Operations Without Ctrl+Shift+Enter

A Sorted Helper Column

The INDEX Function Array Argument: Array Operations Without Ctrl+Shift+Enter

Comparing Two-Value Lookup Solutions: Join Array Formula, DGET, Helper Column, and PivotTable

Chapter Summary

Chapter 6: Function Argument Array Operations

Excel Files

Function Argument Operation

Counting All Characters in a Column with a Function Argument Array Operation

Lookup Addition: Using SUMPRODUCT and SUMIF When a Lookup Table Is Not Sorted

Lookup Addition: Using SUMPRODUCT and LOOKUP When the Lookup Table Is Sorted

Timing the Formulas in This Chapter

Comparing SUMIF and LOOKUP for Lookup Addition

Chapter Summary

Chapter 7: Array Constants

Excel Files

Array Constants to Hard Code Values into Formulas

Column Array Constants (Vertical Array Constants)

Row Array Constants (Horizontal Array Constants)

Table Array Constants (Two-Way Array Constants)

Array Syntax Rules

Using SUM and SMALL to Add the Three Lowest Scores, Excluding Ties

Using SUM and LARGE to Add the Three Largest Scores, Excluding Ties

A Formula for Dynamically Adding the Top n Values

Using ROW and INDIRECT for a Dynamic Variable-Length Array of Sequential Numbers

Excel Table Feature for Adding the Top Three Values

Calculating Net Cost from Four Series Discounts in a Single Cell

Hard Coding Array Constants into VLOOKUP to Save Space

Using Defined Names to Store Array Constants

VLOOKUP col_index_num with an Array Constant

Math and Comparative Array Operations Using Array Constants

Comparing Solutions for Adding the Smallest or Largest n Values

Chapter Summary

Chapter 8: Array Formulas That Deliver More Than One Value

Excel Files

Delivering a Single Result or an Array of Results with an Array Formula

Array Formulas That Deliver More Than One Item to More Than One Cell

Creating an Array of Sequential Numbers: ROW(range)-ROW(FirstCellInRange)+1

Why ROW(range)-ROW(FirstCellInRange)+1 Is Useful

Establishing a Pattern

Holding Down Ctrl While Moving the Cursor

Highlighting a Number and a Blank Cell

Using Fill Series

Formula Number Incrementors: Non-Array Formulas to Increment Sequential Numbers Vertically or Horizontally

Using Formula Number Incrementors and INDEX to Display a Table in a Single Column

Array Constants in Cells for a VLOOKUP Table

Using Ctrl+Shift+Enter on a Formula That Does Not Contain a Direct Array Operation

Timing the Formulas in This Chapter

Chapter Summary

Chapter 9: A First Look at Array Functions: TRANSPOSE, MODE.MULT, and TREND

Excel Files

Array Formulas That Deliver an Array of Results

Array Functions

The TRANSPOSE Array Function

Avoiding Zeros from Empty Cells When Using TRANSPOSE, IF, and ISBLANK

Null Text Strings

MODE Calculations

The MODE.MULT Array Function

Extracting Modes, One at a Time: COUNT, IF, ROWS, INDEX, and MODE.MULT

Extracting Modes, One at a Time: IFERROR, ROWS, INDEX, and MODE.MULT

Using IF Instead of IFERROR to Reduce Formula Calculation Time for Data Extraction Formulas

Efficient Use of the IFERROR Function

Using PivotTable to find modes.

Comparing Calculating Modes Solutions

Using the TREND Array Function to Estimate Many y Values in Linear Model

Comparing SLOPE and INTERCEPT, FORECAST, and TREND

Timing the Formulas in This Chapter

Chapter Summary

Chapter 10: The Amazing SUMPRODUCT Function (and SUMIFS, Too)

Excel Files

SUMPRODUCT and the SUMIF, SUMIFS, COUNTIF, and COUNTIFS Functions

The Amazing and Versatile SUMPRODUCT Function

Parameters for Using the SUMPRODUCT Function

Multiplying Two or More Arrays with the Same Dimensions and Then Adding

Multiplying Three Ranges with Different Dimensions and Then Adding

Adding an Array Operation’s Resultant Array: Just the SUM Part

When to Use SUMPRODUCT to Count or Add with Multiple Criteria

Converting TRUEs and FALSEs to Ones and Zeros, Usually with Double Negatives

Timing the Conversion of TRUEs and FALSEs to Ones and Zeros

Adding with Three Criteria: SUMIFS Instead of DSUM or SUMPRODUCT

Timing the SUMIFS, DSUM, and SUMPRODUCT Functions

Comparative Operator Syntax Differences for SUMIFS, DSUM, and SUMPRODUCT

Workbook References: SUMIFS and the Like Can’t, but SUMPRODUCT Can

Counting How Many Friday the 13ths Fall Between Two Dates

Comparing Methods for Counting or Adding with Criteria

IF Inside SUMPRODUCT

Chapter Summary

Chapter 11: Boolean Logic: AND Criteria and OR Criteria

Excel Files

Boolean Data

Boolean Math: AND Criteria and OR Criteria

AND Criteria

Multiplication, Division, and the IF Function for a Boolean Array Formula AND Criteria

Comparing Multiplication, Division, and the IF Function for Boolean Array Formula AND Criteria

OR Criteria: Be Careful Not to Double Count!

Counting with an OR Logical Test That Cannot Get More Than One TRUE

Timing Counting Formulas for an OR Logical Test That Cannot Get More Than One TRUE

Comparing Formulas to Count with an OR Logical Test That Cannot Get More Than One TRUE

Using a Formula to Count with an OR Logical Test That Can Get More Than One TRUE

Timing Counting Formulas for an OR Logical Test That Can Get More Than One TRUE

Comparing Formulas to Count with an OR Logical Test That Can Get More Than One TRUE

OR Criteria for Adding, Averaging, and Finding min or max

Using AND Criteria and OR Criteria in the Same Formula: OR Logical Test Cannot Get More Than One TRUE

Using AND Criteria and OR Criteria in the Same Formula: OR Logical Test Can Get More Than One TRUE

Filter, Advanced Filter, and PivotTables Can Handle OR Criteria

Chapter Summary

Chapter 12: When Is an Array Formula Really Needed?

Excel Files

Selecting the Fastest-Calculating Function or Formula

Counting Dates When Criteria Is Text: TEXT and SUMPRODUCT or COUNTIFS?

Timing Formulas to Count Dates with Data Mismatch

Adding Yearly Sales with Year Criteria Mismatched Against Serial Dates: SUMPRODUCT, SUMIFS or SUMIF?

Timing Formulas to Add Yearly Sales with Year Criteria Mismatched Against Serial Dates

Chapter Summary

Chapter 13: Dynamic Ranges with the INDEX and OFFSET Functions

Excel Files

Dynamic Ranges

Creating Dynamic Ranges with Formulas

Volatile Functions

Using the Excel Table Feature to Create Dynamic Ranges

INDEX: Formulas to Look Up a Column or Row

INDEX-Delivered Ranges Usually Do Not Require Ctrl+Shift+Enter

Formulas for Dynamic Ranges to Handle Expanding and Contracting Ranges

MATCH: Determining the Last Relative Position in a Data Set

INDEX and MATCH Functions: Retrieving the Last Item in a Range

Using INDEX and MATCH to Create Dynamic Ranges That Expand and Contract

Important Points to Consider for Dynamic Range Formulas

Defined Name Dynamic Range Formulas

A Formula for a Dynamic Table When There Are Data Inconsistencies

Using a Dynamic Range to Define a Table Within a Table: OFFSET or INDEX?

Comparing OFFSET and INDEX for Creating Dynamic Ranges

Charts with Defined Name Dynamic Range Formulas

Using a PivotTable to Create a Dynamic Chart

Using a Dynamic Range to Always Get the Last Five Numbers in a Column

Using a Dynamic Range to Pick Up Only Values Entered Since the Last Text Entry in the First Column

Chapter Summary

Chapter 14: Array Formula Efficiency Rules

Where You Have Been and Where You Are Going

Chapter 15: Extracting Data, Based on Criteria

Excel Files

Mega-Array-Formulas

Extracting Data (Records) from a Table, List, or Database

Using Filter and AutoFilter to Extract Data

Using Advanced Filter for Extracting Records

Why Formulas for Extracting Records Are So Complicated

Using a Non-Array Formula with a Helper Column

Using a Helper Column with AND Criteria and INDEX and MATCH Lookup

Using Helper Cells

Using a Helper Column, OR Criteria, and VLOOKUP as a Lookup Function

Using a Helper Column and AND and OR Criteria to Extract Only Some of the Columns of Data with INDEX and MATCH

Using a Helper Column with AND and OR Criteria to Extract Data and Display Horizontally

Creating an Array of "Matched Record" Relative Positions Inside a Formula

Using an Array Formula for Extracting Data Using SMALL, IF, and INDEX Functions and AND Criteria

Timing IF and IFERROR for Array Formulas That Extract Data

Comparing Formula Calculation Time for a Helper Column and the SMALL IF for Array Formulas That Extract Data

Using an Array Formula for Extracting Data Using AGGREGATE, Boolean Math and INDEX, and AND Criteria

Timing SMALL and AGGREGATE for Array Formulas That Extract Data

Returning Multiple Items from One Lookup Value

Extracting Data with OR Criteria from a Single Column: Boolean or MATCH?

Timing Boolean OR Addition and ISNUMBER/MATCH for Array Formulas That Extract Data

Extracting Data with OR Criteria Operating on More Than One Column and AND Criteria

Extracting Data with OR and AND Criteria, Including Numbers Divisible by 5

Extracting Data Items in List 2 That Are Not in List 1: Comparing Two Lists

Helper Columns in the Data Extract Area

Dynamic Range Inside Array Formula to Extract Records

Comparing Methods for Extracting Data

Some Key Concepts for Data Extraction Formulas

Chapter Summary

Chapter 16: The FREQUENCY Array Function

Excel Files

Array Functions from Earlier Chapters

The FREQUENCY Array Function: Amazing, Powerful, and Versatile

Details of How the FREQUENCY Array Function Works

Need Horizontal? Use TRANSPOSE Wrapped Around FREQUENCY

Empty Cells, Text, and Duplicate Bin Values

Creating Unambiguous Labels for Users

Counting Between Upper and Lower Values: FREQUENCY, COUNTIF, or COUNTIFS?

Chapter Summary

Chapter 17: Unique Counting Formulas and the Power of the FREQUENCY Function

Excel Files

Unique Lists

Using a Single-Cell Formula to Count Unique Numbers: FREQUENCY or COUNTIF?

Timing FREQUENCY and COUNTIF Unique Counting Formulas for Numbers

Using Single-Cell Formulas to Count Unique Text or Mixed Data Items: FREQUENCY or COUNTIF?

Timing FREQUENCY and COUNTIF for Text or Mixed Data Unique Counting Formulas

What if There Are Empty Cells in the Range?

Using a Single-Cell Formula to Count Unique Items with More Than One Condition

What About Wildcards?

Comparing Unique Counting Methods: FREQUENCY, COUNTIF, Helper Columns, PivotTables, and Advanced Filter

Using Helper Column to Count Unique Items

Chapter Summary

Chapter 18: The MMULT Array Function

Excel Files

MMULT Array Function

Using a Formula for Total Costs: MMULT

Using a Formula to Calculate Weighted Average: MMULT

Creating a Multiplication Table: MMULT

Finding the Expected Return for a Portfolio of Stocks: MMULT

Parameters for the MMULT Array Function

The MINVERSE and MUNIT Array Functions

Solving Systems of Equations Using MMULT

Using a Single-Cell Formula for Standard Deviation for a Portfolio of Stocks

Chapter Summary

Chapter 19: Extracting Unique Lists and Sorting Formulas

Excel Files

Formulas to Do the Impossible

Extracting Unique Lists from Lists or Tables with Duplicates

Advanced Filter: Extracting a Unique List from a Single Column

Advanced Filter: Extracting Unique Records with Criteria

PivotTable: Extracting a Unique List from a Single Column

Helper Column Formula: Extracting a Unique List from a Single Column

Array Formula: Extracting a Unique List from a Single Column, Using SMALL

Array Formula: Using a Dynamic Range and Extracting a Unique List from a Single Column

Creating a Unique List Formula for a Data Validation Drop-Down List

Treating Wildcards as Characters

Using a Helper Column or an Array Formula to Extract Unique Records with Criteria

Dynamic Formulas to Extract Customer Names and Subtotals from a Transactional Data Set

Formulas for Sorting

Formulas for Sorting Numbers

Using a Helper Column Formula to Sort (Ascending) Records Based on a Numeric Column

Using an Array Formula to Sort (Ascending) Records Based on a Numeric Column

Using a Formula to Extract the Top Three Scores and Names Associated with Scores (Including Ties)

Using a Helper Column Formula to Sort (Ascending) Records Based on a Text Column

Using an Array Formula to Extract a Unique List and Sort Mixed Data

Chapter Summary

Chapter 20: Conditional Formatting with Array Formulas

Excel Files

Conditional Formatting with Formulas, Including Array Formulas

Chapter Summary

Chapter 21: Data Tables

Excel Files

Data Tables

Using One-Variable Data Tables for One-Formula What-If Analysis

Using One-Variable Data Tables to Replace Many Formulas with One Formula

Using Two-Variable Data Tables

Chapter Summary

Chapter 22: The LINEST Array Function

Excel Files

An Array Function for Many Statistics at Once

Using LINEST to Deliver Slope and Intercept to a Horizontal Range

Using LINEST to Deliver Slope and Intercept to a Vertical Range

Using LINEST to Deliver 10 Statistics for One x Variable

Using a Formula to List LINEST Table Results in a Vertical Column

Using LINEST to Deliver 12 Statistics for Two x Variables (Multiple Regression)

Using LINEST to Deliver 14 Statistics for Three x Variables (Multiple Regression)

Chapter Summary

Chapter 23: Can You Figure Out How the Huge Array Formula Works?

Figuring Out How an Array Formula Works

Looking Up the First Item in a Row with Empty Cells

A Formula to Look Up Column Headers Associated with the First Non-empty Cell in a Row

A Formula to Look Up a Column and Then Match a Condition in the Column and Extract Data

A Formula to Look Up the Longest Word in a Column

A Formula to Calculate Percentile with One Condition

A Formula to Rank Values with One Condition

A Formula to Calculate the Maximum Two-Day Running Total

A Formula to Calculate Net Cost Equivalent, Based on Variable-Length Series Discounts

A Formula to Maximize Consecutive Appearances in Bowl Games, Win or Lose

An Array Formula Cannot Calculate on an Entire Column

Using Defined Names to Avoid Ctrl+Shift+Enter

Chapter 24: Three Bonus Examples

Array Formula 1: Dynamic Cross Tabulated Counting Formula with AND Criteria & OR Criteria in the same formula.

Array Formula 2: List All Modes in a Single Cell

Array Formula 3: Sorted List of Modes in a Single Cell

Conclusion

Appendix: Array Formula Recap

Index

Dedications

Amy Girvin (wife) who has been an awesome wife for 13 years.

Dennis Ho (20 year old son) who likes to hang with his friends.

Isaac Girvin (10 year old son) who likes to race BMX, play Baseball, work with computers and go on adventures.

About the Author

In 2011 I published by first book and DVD, entitled “Slaying Excel Dragons”. Since 2008 I have video blogged at the excelisfun channel at YouTube, which contains over 2700 Excel How-To-Videos, has over 300,000 subscribers and logs over one million views per month. Since 2002 I have taught quantitative/analysis business classes at Highline College using 100% Excel in all classes so that students get a working-world-ready business education. In the 1990’s I ran the boomerang manufacturing company, Gel Boomerangs, and I still remember the awe I felt the first time I changed a formula input for an Excel Income Statement and the whole thing updated. Since that inspiring, life changing moment I have tried to create the same feeling of Excel awe in every class, video and book that I have created.

Awards that I have earned for Excel video blogging: 2011 Highline Faculty of the Year Award, 2012 CWU “Achieving The Dream Award”, YouTube Silver Button Award and the Microsoft Excel MVP Award for the years 2013-2016.

Acknowledgements

Thanks to Steve Kavanaugh, the first person to show me Excel in the 1990s. Thanks to Bill "MrExcel" Jelen for inspiring me to learn Excel well with his books and podcasts, and thanks also to Mr Excel for noticing the videos I had posted on YouTube and inviting me to make videos with him and write books for his company. Thanks to Dusty Wilson for helping me with my matrix algebra. Thanks to the editors, Bob Umlas and Kitty Wilson, for helping me to make this a good book.

But wait…

The REAL thanks that I must give is to all the amazing Excel masters at the MrExcel Message Board. The MrExcel Message Board is where I really learned about advanced formulas and array formulas. This amazing source for information on how formulas really work is incalculably valuable. This book is simply my attempt to put the ideas that I have learned from the MrExcel Message Board masters together in a logical order. I’d like to thank the people from the MrExcel Message Board and YouTube comments section who have helped me over the years and who are the source of the ideas in this book.

Specifically from the MrExcel Message Board, I must say a few words about the masters I have learned from:

Thanks to Aladin Akyurek for all the amazing formula knowledge and always helping me and many others to "robustify" our formulas. As so many people write, it all starts with Aladin!Thanks to barry houdini for some amazing date formulas and other cool formula stuff.Thanks to Charles "Fast" Williams for his amazing articles on how to speed up calculation time.Thanks to Domenic for always answering my detailed array formula questions.Thanks to DonkeyOte for amazing formulas, including two awesome reverse lookup formulas!Thanks to pgc01 for amazing statistical formulas and VBA!

And thanks all the people who have answered my posts at the MrExcel Message Board: Aladin Akyurek, Andrew Poulsom, barry houdini, Bill "MrExcel" Jelen, Charles "Fast" Williams, circledchicken, Colin Legg, Dave Patton, Domenic, DonkeyOte, HOTPEPPER, jeffreybrown, Jon von der Heyden, Marcelo Branco, Norie, Peter_SSs , pgc01, Richard Schollar , Ron Coderre, Smitty, T. Valko, Tom Urtis, VoG, xenou, Yogi Anand, Teethless mama, BigC, Boller, Beate Schmitz, bosco_yip, c_m_s_jr, crimson_b1ade, Domski, edokhotnik, Erdinç E. Karaçam, erik.van.geit, Fazza, gavinkelly, GlennUK, HalfAce, Haseeb Avarakkan, hiker95, hkaplan2, iknowu99, iliace, James006, jasonb75, jbeaucaire, jindon, jonmo1, krn6264, Latchmaker, lenze, Lweiy, Makrini, Mark O’Brien, Matty, MickG, mikerickson, MrKowz, mvptomlinson, NateO, nbrcrunch, Oaktree, PaddyD, PCL, pto160, RalfA, ravishankar, robind21283, RomulusMilea, RoryA, sanrv1f, schielrn, scottylad2, shemayisroel, shg, sous2817, stanleydgromjr, Starkman, steimel386, texasalynn, timorrill, Travis, tusharm, UniMord, Weaver, wigi, and wsjackman.

A special thanks must go to Stuart Ainslie for the amazing editing job he did for both the writing and the technical! Thanks Stuart!!!!

Thanks also to a few amazing YouTube Excelers who have helped me tremendously: circledchicken, krn14242, hamy72, AThehos, SchultzesBlues, dmr450, XLarium and Bill Szysz.

Go, Excel online team!!!

Introduction

Why in the world would you read a book about array formulas?

Seriously, array formulas are rarely needed and can be insanely difficult. Here are some potential reasons you might want to read a book about them:

You love Excel magic and wizardry. Your idea of fun is making Excel do the impossible with a formula.You know how to create formulas and want to take the next leap in Excel, into the upper echelon of creating advanced formulas.You have heard about array formulas and know that in some situations, they are the most efficient solution, but you have no idea how to create them.You use array formulas and are aware of some of the drawbacks they present (such as slow formula calculation time), and you would like to learn how to create more efficient array formulas.

Why are array formulas important tools to have in your Excel toolkit?

Here are some reasons array formulas may be useful to you:

Sometimes using array formulas is the only way to accomplish a given task. See Figure IN.1Array formulas can replace intermediate steps and provide a single-cell solution. See Figure IN.2.In general, the advantage of using formulas over using Excel features such as sorting, filtering, and PivotTables is that when you change a formula input, the formulas update instantly. By learning about array formulas, you increase your range of formula skills and are less limited when it comes to building Excel solutions. See Figure IN.3.If you understand how array formulas work, you understand the true beauty and power of Excel formulas! See Figure IN.4.

Figure IN.1 If you can’t use a PivotTable, you don’t have the AGGREGATE Excel 2010 function, and you need to copy the formula down a column (you can’t use DMIN function), using the array formula in cell E3 is an efficient solution. See how to create this formula in Chapter 4.

Figure IN.2 If you want to avoid using the lookup formula in column D before calculating the total costs, you can use the array formula in cell G7 as an efficient single-cell solution. See how to create this formula in Chapter 6.

Figure IN.3 When the criteria for record extraction changes often, it can be beneficial to use formulas rather than the Filter feature. The array formula in cell G8 is an efficient solution for extracting with three criteria. See how to create this formula in Chapter 15.

Figure IN.4 As shown in cell B4, this array formula does the seemingly impossible, counting how many "Friday the 13ths" there are between a start date and an end date. With the power and beauty of array formulas under your belt, almost anything is possible with formulas! See how to create this formula in Chapter 10.

Why would Mike excelisfun Girvin write a book about array formulas?

Here are some potential difficulties with writing a book about the topic of Excel array formulas:

It’s a niche topic.It’s a very difficult topic.Microsoft doesn’t provide much documentation about array formulas.Excel users disagree about what an array formula is.There is no systematic listing of all the aspects of array formulas, and there is not a good set of rules or guidelines for array formulas.

Why would I, Mike excelisfun Girvin, write a book about Excel array formulas—especially when there are hundreds of Excel masters who are much smarter than I about Excel and Excel array formulas? As I always say at YouTube, "I am just a guy having fun with Excel!" I’ve written this book because I have long wished for a book that systematically lists the aspects, elements and guidelines for array formulas. What I have done in this book is just gather up what I have learned over the years from places like the MrExcel Message Board (mrexcel.com/forum) and other Excel sites, and I’ve tried to present the details I have gleaned in a systematic way. I do not promise that this book tells the complete story of Excel array formulas or even that it is 100% correct, but I do promise that I have done my best to present what I have learned about Excel array formulas.

About the Book

This book is similar to the first book I wrote, Slaying Excel Dragons, in that it is a story from beginning to end about efficiencies in Excel.

Whereas in Slaying I wrote about how to build a foundation of basic skills across all of Excel, this book concentrates on a set of guidelines for how to create efficient array formulas. That said, this book actually covers three topics: formulas, advanced formulas, and array formulas. Because an array formula is just a more narrow type of advanced formula or formula, much of what this book says can be applied across all three categories of formulas. This is good news because it means that this book is not just about building efficient array formulas, but it is about building efficient solutions using formulas. Further, throughout the book I compare and contrast a given potential array formula solution against other Excel features, such as filtering, PivotTables, and non-array formulas. This will help you understand the context or situation in which array formulas may be the most efficient solution.

Finally, this book is not just a listing of cool array formulas or of readymade solutions. Instead, it is a story that starts at the point of "no knowledge of array formulas" and builds, piece by piece, toward an endpoint of "now we have a set of guidelines and rules we can use to build our own array formulas." To this end, this book gradually builds a set of "Array Formula Efficiency Rules," one by one, which Chapter 14 lists all in one place. Then, in later chapters, you’ll be able to create truly mega Excel formulas. In the early parts of this book, I give a lot of detail and move slowly through each topic, especially about how each individual part of a formula is working. As the book moves along, I give fewer details, move more quickly and show more examples.

A final note about this book: I think that I have done a less than stellar job with the writing in this book. I believe that sometimes as I was writing I got lost in the complexities of the topic and wrote in a somewhat less than optimal manner. However, if you can wade through the less than stellar writing, there are many valuable formula lessons in this book that can improve your formula creation abilities.

Files That Accompany the Book

As you read this book, you’ll want to follow along with the examples, in many cases trying them out yourself. To make this easier for you, I’ve posted the Excel files that contain the finished examples from this book online. You can get them from the "Ctrl+Shift+Enter: Mastering Excel Array Formulas" section of this website:

http://people.highline.edu/mgirvin/excelisfun.htm

At this site you can download a zipped folder named FilesForCtrlShiftEnterBook. After you download the zipped folder, you will have to unzip the folder to get access to the files inside the zipped folder.

Inside the downloaded folder is a file named CtrlShiftEnterBookFinishedFile.xlsm. This file has all the finished examples from this book. To find the worksheet in this workbook that relates to the example you are interested in, simply navigate to the worksheet that has the same name as the figure number in the book. For example, if the figure number is IN.1, the sheet name you should look for is IN.1.

In addition to the workbook with the finished examples from the book, I’ve made available a number of other files with large data sets that have timing results for different formulas. In each section of the book that discusses timing results, I’ve provided the name of the corresponding file or files you should use.

Note: If you are used to watching excelisfun videos at YouTube or if you have read the book Slaying Excel Dragons, please note that there is no Start file for this book. This is because most of the examples in the book are already completed for you. If you want to try the formulas as shown in the book, a good strategy would be to copy the file with the finished examples and then delete the formulas from the cells before you try the formulas on your own.

Note: I have published both a book and a DVD titled Ctrl+Shift+Enter: Mastering Excel Array Formulas. The files that go along with this book and that DVD are not the same files. If you are reading the book, download the files that go along with the book.

The Ctrl+Shift+Enter: Mastering Excel ArrayFormulas DVD

The DVD Ctrl+Shift+Enter: Mastering Excel Array Formulas is a separate product from this book. The two products cover the same broad topics related to creating efficient array formulas. However, the video has kinetic visuals of how array formulas work and less detail than the book. The book has a more complete story and systematic presentation of array formulas. If you really want to master array formulas, study both the book and the DVD.

Who This Book Is For

Because this is an advanced Excel formula book, it assumes that you have the basics of how to build formulas under your belt. In Chapter 1, I list the key formula concepts that you should be familiar with in order to get the most out of this book.

Note: If you need a full refresher course on all the aspects of Excel formulas, pick up my DVD Ctrl+Shift+Enter: Mastering Excel Array Formulas, just described. The first video on this DVD is a one-hour powerhouse of 31 examples that can quickly get you up to speed before you move on to array formulas.

Special Formatting of Cells with Formulas, Labels, and Raw Data

All the Excel examples in this book use fill colors to indicate cell contents:

A pale green fill color indicates a cell that contains a formula.A dark blue fill color indicates a cell that contains a label.Cells that contain raw data or formula inputs have no fill color added.

I follow these conventions here and in my YouTube videos to make it clear which cells contain formulas, labels, and raw data. For tasks outside this learning process, other formatting conventions are usually preferred. Although this book is in black and white, you can see this convention in the Excel workbook that accompanies the book.

Remembering the Old Conditional Sum Wizard

I still remember the first array formula I created, using the Conditional Sum Wizard dialog box from the old Tools menu in earlier versions of Excel. In those days, I could get the wizard to work and get the correct result. But after the formula was entered into the cell through the dialog box, mysterious curly braces would appear in the cell with the formula. Then if I tried to edit the cell without using the Conditional Sum Wizard dialog box, I would get an error. Looking back at the Conditional Sum Wizard dialog box now, I can see that the dialog box itself was pretty easy to follow, but it didn’t mention the term array formula, and it didn’t mention the need to enter Ctrl+Shift+Enter with an array formula. If only there had been better instructions.

Hopefully this book will provide better guidance than the old Conditional Sum Wizard. To start, let’s move on to Chapter 1 and take a look at the key concepts involved in building formulas.

Chapter 2: Introduction to Array Formulas

Excel Files

To follow along with the examples in this chapter, you can download the accompanying files, as explained in the Introduction.

What Is an Array?

An array is a collection of two or more items. This is the logical starting point for the book. Everything else follows from this.

Array Formula Efficiency Rule 2

An array is a collection of two or more items.

The Types of Arrays in Excel

There are three types of arrays in Excel:

A reference array contains more than one cell. Examples include a range of cells, a worksheet reference, and a defined name.An array created by a formula element, also called a resultant array, is an array of items created by the array operation.An array constant is an array of values hard coded into a formula.

Array Formula Efficiency Rule 3

There are three types of arrays in Excel: a reference array, an array created by a formula element, and an array constant.

What Is An Array Formula?

An array formula is a formula that contains an operation (math, comparative, join, or function argument) on an array of items rather than on single items, and, the operation delivers a resultant array of items rather than a single item. This operation is called an array operation and is distinguished from an aggregate operation, which delivers a single item. The resultant array of items (also called an array created by a formula element) can be used as a formula element in a larger formula, or it can be the final answer that the array formula delivers to a range of cells. The final answer from an array formula can either be a single item or an array of items.

Array Formula Efficiency Rule 4

An array formula is a formula that contains an operation on an array of items rather than on single items, and, the operation delivers a resultant array of items rather than a single item.

Performing an Operation on an Array of Items Rather Than on Single Items

To understand what it means to perform an operation on an array of items rather than on single items, let’s first look at an operation on single items. Figure 2.1 shows a table of opening and closing stock prices. The goal of the formula in this case is to calculate the change between the close prices and open prices.

Figure 2.1 A math operation on single items.

The formula in Figure 2.1 operates on single items. Here are the details:

Cell E3 contains a formula that subtracts the value in cell C3 from the value in D3.The operation is the math operation subtraction.The single items being operated on are the numbers in cells D3 and C3.This is not an array formula because the operation being performed is being performed on single items only, and the result from the operation is a single item.

Figure 2.2 shows another table of opening and closing stock prices. In this case, the formula goal is to calculate the maximum stock value change over the four-day period.

Figure 2.2 A math operation on an array of items.

The formula in Figure 2.2 operates on an array of items. Here are the details:

Cell D8 contains a formula that subtracts the values in the range C3:C6 from the values in D3:D6.The operation is the math operation subtraction.The two arrays being operated on are the numbers in the ranges D3:D6 and C3:C6.This is an array formula because the operation is being performed directly on an array of items. I always think of it as "Is the operator touching the array?" (The function argument operation and array functions examples later in the book are slight variations on this idea.) In this case, the subtraction operator is touching the range (an array), and therefore it is performing an operation on an array. In addition, as you will see in the next section, this operation will produce a resultant array of numbers.

Here is the comparison between operations on single items and operations on arrays:

This formula contains an operation on single items: =D3-C3This formula contains an operation on two arrays: D3:D6-C3:C6

Let’s now move on and examine the previous two examples in more detail.

First Example: Single-Cell Array Formula or Helper Column?

In Figure 2.3, which shows a table of opening and closing stock prices, the formula goal is to calculate the maximum stock value change over the four-day period.

Figure 2.3 If the goal is to see all the individual calculations and then calculate the maximum change, using a helper column is a great idea.

Figure 2.3 shows four individual formula calculations made in the Change column. This column is called a helper column because it helps you get the values needed to calculate the maximum stock change. In cell B8, the MAX function looks through the values 3, -8, 7, and 6 and picks out the maximum value, which is 7. This calculation made by the MAX function is called an aggregateoperation because it looks through all the values and calculates one answer; it is not an array operation, even though it deals with more than one item.

If your project requirements mandate that you show all the individual changes, then the helper column and the MAX function aggregate calculation is a great solution. But what if you do not want to see all the detail, and you just want to calculate the maximum change? Or what if you have thousands of rows of data, and you cannot afford to use the spreadsheet real estate to create an extra column? In such a situation, it would be useful to have a single-cell formula solution.

How do you get all those individual stock change calculations into a single-cell formula? Look again at Figure 2.3 and notice that the current MAX function is looking at the values 3, -8, 7, and 6. If you could create that helper column in your single-cell array formula, you would have your solution. With an array calculation, you can accomplish exactly that!

Figure 2.4 Creating an array calculation in the MAX function number1 argument.

As shown in Figure 2.4, when you create the array calculation, you highlight the entire Close column range, then type a subtraction operator, and then highlight the entire Open column range inside the MAX function’s number1 argument.

Using the "Evaluate Formula Element" Trick to Show the Resultant Array

Before you enter a formula, you want to prove to yourself that the array calculation delivers a resultant array of numbers that simulates the helper column shown in Figure 2.3. In order to prove this to yourself, you can evaluate your array calculation while the formula is still in Edit mode.

As shown in Figure 2.5, the first step in evaluating the array operation is to highlight the entire array operation. You do this by clicking the number1 argument in the ScreenTip (the gray prompt below the formula being created in Edit mode).

Figure 2.5 In order to highlight the array operation while the formula is in Edit mode, click the number1 argument in the ScreenTip.

Figure 2.6 In order to see the resultant array of numbers created by the array operation, you press the F9 key to evaluate the selected formula element.

As shown in Figure 2.6, the next step is to press the F9 key. You do this to evaluate the selected formula element. When you evaluate the array operation, you can see that the array operation does simulate the values in the helper column. That is amazing! What a space saver! You have created the numbers you need inside the formula without using extra cells in the spreadsheet.

Notice that the formula element D3:D6-C3:C6 creates the array of values {3;-8;7;6}. Remember from the list of the different types of arrays you can have in Excel that {3;-8;7;6} is an example of an array created by a formula element; it is the resultant array delivered by the array operation.

Note: In Chapter 7 you will learn about the syntax used when the formula element D3:D6-C3:C6 is evaluated to {3;-8;7;6}. The term used to describe this array of values is array constant. In an array constant, curly braces house the array, a semicolon indicates a row, and a comma indicates a column.

However, after you press the F9 key, you must immediately undo the formula element evaluation because you do not want to leave the resultant array hard coded into the formula. As shown in Figure 2.7, you undo the evaluation by using the keyboard shortcut for undo: Ctrl+Z.

Figure 2.7 Don’t forget to undo the evaluation by using Ctrl+Z.

This ability to evaluate a formula element and see what the resultant array will be before you enter the formula into the cell is a monumentally important trick for creating array formulas. It allows you to check whether your formula creation is on the right track. I like to call this trick the "Evaluate Formula Element" trick. These are the steps in the trick:

1.Select the formula element.

2.Press F9.

3.Look at the resultant array and check whether your formula creation is on the right track.

4.Press Ctrl+Z to undo the evaluation.

This trick is particularly useful with mega-formulas that have many formula elements.

Array Formula Efficiency Rule 5

You can use the Evaluate Formula Element trick to see what an array operation evaluates to before you enter the formula into the cell by following these steps: (1) Select the formula element, (2) press F9, (3) look at the resultant array and check to see if the formula creation is on the right track, and (4) press Ctrl+Z to undo the evaluation.

By using the Evaluate Formula Element trick, you can see the simulated column of values in a single cell. With an array operation, you can create a column of values in a single cell. This ability to skip over a helper column and create an array of values in a single cell can be revolutionary for formula creation: It means that whenever you have a column of calculations and you either don’t care about all the individual calculations or you can’t afford the spreadsheet real estate, it is almost certainly possible to just do away with the column of calculations and instead create an array calculation in a single cell.

Later in this book, you will consider what pushing the calculations from a helper column to a single cell will do to calculation time.

Array Formula Efficiency Rule 6

Whenever you have a column of calculations and you either don’t care about all the individual calculations or you can’t afford the spreadsheet real estate, it is almost certainly possible to just do away with the column of calculations and instead create an array calculation in a single cell. The caveat is that you must also consider formula calculation time for single-cell calculations.

Entering an Array Formula into a Cell

In the example you’ve been working on in this chapter, once you have proven to yourself that the array calculation simulates the column of stock value changes, you need to enter into the cell the MAX function with its array calculation in the number1 argument. Then you press Enter. Figure 2.8 shows that you get a #VALUE! error. By sending you this #VALUE! error, Excel is telling you that you did not enter the array formula correctly. (Yes, there are correct and incorrect ways of entering an array formula into a cell!)

Figure 2.8 Using Enter to put the MAX function with its array calculation into the cell does not work.

You need to consider whether the function argument that the array calculation sits in can innately handle array operations. (Only four functions have arguments that can innately handle array operations without Ctrl+Shift+Enter: SUMPRODUCT, LOOKUP, AGGREGATE, and INDEX. You’ll learn more about them later in this chapter.)

Figure 2.9 shows that the array calculation is housed in the number1 argument in the MAX function. The number1 argument in the MAX function is not programmed to innately handle array operations. Therefore, you must tell Excel that this argument contains an array calculation and that you would like Excel to perform that array calculation. You say this to Excel by using the special keystroke Ctrl+Shift+Enter. With the formula in Edit mode, the best way to enact this keystroke is to hold down Ctrl and Shift and then press the Enter key.

Figure 2.9 The array calculation is housed in the number1 argument in the MAX function.

Figure 2.10 For an array calculation in the number1 argument in the MAX function, you must enter the formula with Ctrl+Shift+Enter.

Figure 2.10 shows that when you use Ctrl+Shift+Enter, you successfully enter the formula into cell B8. You can see that the MAX function correctly selects the value 7 from among the values 3, -8, 7, and 6.

After you enter an array formula with Ctrl+Shift+Enter, you should immediately shift your eyes up to the formula bar and look to determine whether a left curly brace has been placed at the beginning of the formula and a right curly bracket has been placed at the end of the formula. Figure 2.11 shows what the formula should look like after you press Ctrl+Shift+Enter. Notice the curly braces in the formula. How did those curly braces get there, if you didn’t type them in? Excel automatically places those curly braces when you press Ctrl+Shift+Enter. By placing those curly braces, Excel tells you that it understands that this is a formula with an array calculation and that you would like Excel to calculate it correctly.

Figure 2.11 The curly braces indicate that Excel knows that this formula contains an array calculation.

In terms of entering an array formula into a cell, so far you have learned the following:

If the function argument does not innately handle array operations, you must enter the formula with Ctrl+Shift+Enter.After you enter a formula with Ctrl+Shift+Enter, Excel places curly braces at the beginning and end of the formula. If you try to type in the curly braces, they will appear as text, not as part of an array formula.If you don’t use Ctrl+Shift+Enter, you may get a #VALUE! error.

Note: If you press Ctrl+Shift+Enter on a non-array formula your formula will calculate correctly and it will have curly braces around it which are not necessary.

But this is not the whole story. The #VALUE! error is not the only answer you can get if you forget to use Ctrl+Shift+Enter. Figure 2.12 shows the formula =MAX(D3:D6-C3:C6) entered into eight cells. Seven of the cells contain formulas entered without using Ctrl+Shift+Enter. Only in cell B8 is the array formula entered correctly with Ctrl+Shift+Enter. The formulas entered next to the Open and Close columns of data show numeric answers from a process called implicit intersection. All these answers are incorrect except for the one cell that corresponds to the opening and closing numbers that calculate the correct maximum stock change. If the formula is entered without Ctrl+Shift+Enter in a cell that is not next to the Open and Close columns of data, a #VALUE! error is displayed. This phenomenon happens on the left or right of a vertical data set and above or below a horizontal data set.

A common array formula error is to forgetfully enter an array formula (that requires Ctrl+Shift+Enter) next to a vertical data set without using Ctrl+Shift+Enter and then to mistakenly interpret the numeric answer as correct. However, seeing a numeric answer or not seeing a #VALUE! error is not proof that a formula has been entered correctly. For a formula that requires Ctrl+Shift+Enter, curly braces are the best proof that the formula has been entered correctly. To avoid the pitfall of implicit intersection, it is always best to immediately check the formula bar to see if the curly braces are in place.

Figure 2.12 Be careful of an implicit intersection answer when creating array formulas. Only the formula in cell B8 is correct.

Notes About the Formula =MAX(D3:D6-C3:C6)

The following table shows a summary of the MAX array formula you just learned about.

Impetus for Creating an Array Formula:

To save space, you want a single-cell formula with no helper column.

You don’t need all the detail, just the one number that is biggest.

Calculation Summary:

1. =MAX(D3:D6-C3:C6)

The two arrays are the ranges D3:D6 and C3:C6.

You are performing a math operation (subtraction) on two arrays.

This is an array formula because you are performing an operation on an array, and the operation produces a resultant array.

2. =MAX({762;757;763;768}-{759;765;756;762})

The two columns need to have corresponding numbers subtracted.

3. =MAX({762-759;757-765;763-756;768-762})

The corresponding numbers are subtracted.

4. =MAX({3;-8;7;6})

The array operation D3:D6-C3:C6 evaluates to {3;-8;7;6}.

The array operation D3:D6-C3:C6 delivers the resultant array {3;-8;7;6}.

{3;-8;7;6} is an example of an array created by a formula element.

The formula element D3:D6-C3:C6 delivers the array {3;-8;7;6} to the MAX function.

5. The formula delivers the single value 7.

Entering a Formula into a Cell:

The array calculation is located in the number1 argument in the MAX function. This argument is not innately programmed to handle array operations. To get the array calculation to calculate correctly, you must press Ctrl+Shift+Enter to enter the formula into the cell.

This is the formula as it appears in the formula bar after you enter it with Ctrl+Shift+Enter: {=MAX(D3:D6-C3:C6)}

If you do not use Ctrl+Shift+Enter, you will get a #VALUE! error or potentially an incorrect answer caused by implicit intersection.

Conclusion:

If you do not want to use a helper column and you don’t need to see all the detail, using this array formula is a great option.

Notes:

In Chapter 4, you will see an alternative to using the MAX array formula that uses the new Excel 2010 function AGGREGATE.

In Chapter 7 you will learn about the syntax used when the formula element D3:D6-C3:C6 is evaluated to {3;-8;7;6}. This is an array constant, and you will see that curly braces house the array, a semicolon indicates a row, and a comma indicates a column.

For the array formula =MAX(D3:D6-C3:C6), you had to enter the formula using Ctrl+Shift+Enter and then verify that the formula was entered correctly by looking at the formula bar to see if the curly braces were automatically added. But must you always enter array formulas by using Ctrl+Shift+Enter? No.

Four functions have arguments that can innately handle array operations without Ctrl+Shift+Enter: SUMPRODUCT, LOOKUP, AGGREGATE, and INDEX. To complete our discussions about how to enter an array formula into a cell, you need to understand how these types of functions handle array operations. The next section looks at SUMPRODUCT. (You’ll learn much more about SUMPRODUCT and these other functions in later chapters.)

SUMPRODUCT Array Operations