Ctrl+Shift+Enter Mastering Excel Array Formulas

Do the Impossible with Excel Formulas Thanks to Array Formula Magic

Mike ExcelisFun Girvin

• Publisher: Holy Macro! Books
• Year: 2013
• eBook ISBN: 9781615472093
• Edition: 0
• Topic: Computer Science
• Subtopic: Desktop Applications
• Index: Computer Science

Designed with Excel gurus in mind, this handbook outlines how to create formulas that can be used to solve everyday problems with a series of data values that standard Excel formulas cannot or would be too arduous to attempt. Beginning with an introduction to array formulas, this manual examines topics such as how they differ from ordinary formulas, the benefits and drawbacks of their use, functions that can and cannot handle array calculations, and array constants and functions. Among the practical applications surveyed include how to extract data from tables and unique lists, how to get results that match any criteria, and how to utilize various methods for unique counts. This book contains 529 screen shots.

Table of contents

• Preface/forward/intro
• Title page
• Copyright page
• First chapter
• _GoBack
• 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 Array Formulas 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