Control+Shift+Enter:
Mastering Excel Array Formulas
*Print
Book
Command The Power of the 'Array Formula' in
Microsoft Excel!
Array Formulas are a powerful tool in Excel. An Array Formula works
with a series of Data Values instead of a single Value. Excel Array
Formulas are mysterious, even to people who use Excel 40 hours a
week. Start with the secret keystroke that must be used to finish an
Array Formula: Ctrl+Shift+Enter. Then, you have to know which
functions will work with Arrays and which will not. Once you get a
grasp on Array Formulas, you will find they solve problems that
regular Excel Formulas can not solve, or would require hundreds of
thousands of regular Formulas.
Although elusive, Array Formulas can be used to solve everyday Excel
problems. If you need to figure out how many times your birthday
fell on a Friday, an Array Formula can solve that. If you need to
return a unique list of sales reps from a Database, an Array Formula
can solve that.
How does this book approach Array Formulas?
This book systematically lists the aspects, elements and guidelines
for array formulas. Array formulas can be insanely difficult. But
there are times when an array formula is the only way to accomplish
a task. Array formulas can beat filtering, sorting and pivot tables
because array formulas will automatically update when the input
cells change.
Why should you read this book:
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 cringe when a coworker sends a workbook with formulas wrapped
in curly braces.
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.
Contents Table of Ctrl+Shift+Enter:

Introduction: 1

Chapter 1: Formula Basics 7

Chapter 2: Introduction to Array Formulas 11

Chapter 3: Math Array Operations 27

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

Chapter 5: Join Array Operations 61

Chapter 6: Function Argument Array Operations 73

Chapter 7: Array Constants 81

Chapter 8: Array Formulas That Deliver More Than One
Value 101

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

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

Chapter 11: Boolean Logic: AND Criteria and OR Criteria
151

Chapter 12: When Is an Array Formula Really Needed? 171

Chapter 13: Dynamic Ranges with the INDEX and OFFSET
Functions 177

Chapter 14: Array Formula Efficiency Rules 203

Chapter 15: Extracting Data, Based on Criteria 215

Chapter 16: The FREQUENCY Array Function 253

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

Chapter 18: The MMULT Array Function 275

Chapter 19: Extracting Unique Lists and Sorting
Formulas 291

Chapter 20: Conditional Formatting with Array Formulas
315

Chapter 21: Data Tables 319

Chapter 22: The LINEST Array Function 323

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

Conclusion: 341
About the Author
Mike Girvin teaches Fnance,
Accounting, and Statistics Using Excel at Highline Community College.
He has more than 1,800 Excel 'Howto' videos on YouTube and has been
teaching people how to use Excel for the past 10 years. He lives in
Seattle, Washington. He is also the author of 'Slaying
Excel Dragons"
as well as the "ExcelIsFun"
DVD and the "Ctrl+Shift+Enter"
DVD
*A Note About This Product:
The above BOOK, "Ctrl+Shift+Enter", by Mike "ExcelIsFun" Girvin,
Does Not follow along the same
format as the
DVD of the same title. This BOOK is a standalone
product with it's own format.
"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."
The
DVD of the same title ["Control+Shift+Enter", by Mike
Girvin] does NOT contain this book as part of the
DVD Content as the formats between the two
products differ greatly.
Product Details Print:
344 Pages
ISBN: 9781615470075 (Print)
ISBN: 9781615472093 (pdf)
ISBN: 9781615473298 (epub)
ISBN: 9781615471096 (mobi)
Purchase the eBook Version: $19.99 USD
Purchase the Print Edition:
$29.95 USD
Back to Top...
