ebook img

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

353 Pages·2013·16.95 MB·English
Save to my drive
Quick download
Download
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

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

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

Description:
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 top
See more

The list of books you might like

Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.