Ctrl+Shift+Enter: Mastering Excel Array Formulas

Ctrl+Shift+Enter: A Book About Building Efficient Formulas, Advanced Formulas, and Array Formulas for Data Analysis and Calculating Problems

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

Chapter 1: Formula Basics
Chapter 2: Introduction to Array Formulas
Chapter 3: Math Array Operations
Chapter 4: Comparative Array Operations and Aggregate Calculations with One or More Conditions
Chapter 5: Join Array Operations
Chapter 6: Function Argument Array Operations
Chapter 7: Array Constants
Chapter 8: Array Formulas That Deliver More Than One Value
Chapter 9: A First Look at Array Functions: TRANSPOSE, MODE.MULT, and TREND
Chapter 10: The Amazing SUMPRODUCT Function (and SUMIFS, Too)
Chapter 11: Boolean Logic: AND Criteria and OR Criteria
Chapter 12: When Is an Array Formula Really Needed?
Chapter 13: Dynamic Ranges with the INDEX and OFFSET Functions
Chapter 14: Array Formula Efficiency Rules
Chapter 15: Extracting Data, Based on Criteria
Chapter 16: The FREQUENCY Array Function
Chapter 17: Unique Counting Formulas and the Power of the FREQUENCY Function
Chapter 18: The MMULT Array Function
Chapter 19: Extracting Unique Lists and Sorting Formulas
Chapter 20: Conditional Formatting with Array Formulas
Chapter 21: Data Tables
Chapter 22: The LINEST Array Function
Chapter 23: Can You Figure Out How the Huge Array Formula Works?