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

353 Pages·2013·16.95 MB·English

by  Mike Excelisfun Girvin

Ctrl+Shift+Enter
Mike Girvin

Holy Macro! Books
PO Box 82 Uniontown, OH 44685
ii
Ctrl+Shift+Enter: Mastering Excel Array Formulas
© 2013 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 stor-
age 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 informa-
tion 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 informa-
tion contained in this book.
Author: Mike Excelisfun Girvin
Editor: Kitty Wilson
Layout: Tyler Nash
Published by: Holy Macro! Holy Macro! Books, PO Box 82 Uniontown, OH 44685, USA
Printed in USA
First printing: July 2013
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
iii
Contents
Dedications..................................................................................................................................v
About the Author .......................................................................................................................vi
Acknowledgements ....................................................................................................................vii
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 Condi-
tions ..............................................................................................................................................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
iv
Dedications
To Amy Girvin, my wife, who puts up with all this "Excel stuff."
To Dennis Ho, my 17-year-old son, who likes to hang with his friends and play tennis.
To Isaac Girvin, my 7-year-old son, who likes to race BMX, play baseball, and go on adventures.
v
About the Author
In 2011, Mike Girvin published his first book and DVD, titled Slaying Excel Dragons. Since 2008,
he has video blogged at the excelisfun channel at YouTube, which contains more than 2,000 Excel
how-to videos and logs about 30,000 views per day. Since 2002, he has taught quantitative busi-
ness classes at Highline Community College, using 100% Excel in all classes so that students get a
working-world-ready business education. In the 1990s, he ran a boomerang manufacturing compa-
ny called Gel Boomerangs.
Mike still remembers the awe he felt the first time he changed a formula input for an Excel income
statement, and the whole thing updated. Since that inspiring, life-changing moment, Mike has tried
to create the same feeling of Excel awe in every class, video, and book he has created.
Mike has won a number of awards for Excel video blogging, including the 2011 Highline Faculty
of the Year Award, the 2012 Central Washington University Achieving the Dream Award, and the
2013 Microsoft Excel MVP Award.
vi
Acknowledgements
Thanks to Steve Kavanaugh, the first person to show me Excel in the 1990s. Thanks to Bill "MrEx-
cel" 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 ar-
ray 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 calcula-
tion 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, circled-
chicken, 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, timor-
rill, Travis, tusharm, UniMord, Weaver, wigi, and wsjackman.
Thanks also to a few amazing YouTube Excelers who have helped me tremendously: circledchick-
en, krn14242, hamy72, AThehos, SchultzesBlues, and dmr450.
Go, Excel online team!!!
vii
viii Ctrl+Shift+Enter
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.1
● Array 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.
1