Ctrl+Shift+Enter Mastering Excel Array Formulas
eBook - ePub

Ctrl+Shift+Enter Mastering Excel Array Formulas

Do the Impossible with Excel Formulas Thanks to Array Formula Magic

  1. 338 pages
  2. English
  3. ePUB (mobile friendly)
  4. Available on iOS & Android
eBook - ePub

Ctrl+Shift+Enter Mastering Excel Array Formulas

Do the Impossible with Excel Formulas Thanks to Array Formula Magic

About this book

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.

Frequently asked questions

Yes, you can cancel anytime from the Subscription tab in your account settings on the Perlego website. Your subscription will stay active until the end of your current billing period. Learn how to cancel your subscription.
At the moment all of our mobile-responsive ePub books are available to download via the app. Most of our PDFs are also available to download and we're working on making the final remaining ones downloadable now. Learn more here.
Perlego offers two plans: Essential and Complete
  • Essential is ideal for learners and professionals who enjoy exploring a wide range of subjects. Access the Essential Library with 800,000+ trusted titles and best-sellers across business, personal growth, and the humanities. Includes unlimited reading time and Standard Read Aloud voice.
  • Complete: Perfect for advanced learners and researchers needing full, unrestricted access. Unlock 1.4M+ books across hundreds of subjects, including academic and specialized titles. The Complete Plan also includes advanced features like Premium Read Aloud and Research Assistant.
Both plans are available with monthly, semester, or annual billing cycles.
We are an online textbook subscription service, where you can get access to an entire online library for less than the price of a single book per month. With over 1 million books across 1000+ topics, we’ve got you covered! Learn more here.
Look out for the read-aloud symbol on your next book to see if you can listen to it. The read-aloud tool reads text aloud for you, highlighting the text as it is being read. You can pause it, speed it up and slow it down. Learn more here.
Yes! You can use the Perlego app on both iOS or Android devices to read anytime, anywhere — even offline. Perfect for commutes or when you’re on the go.
Please note we cannot support devices running on iOS 13 and Android 7 or earlier. Learn more about using the app.
Yes, you can access Ctrl+Shift+Enter Mastering Excel Array Formulas by Mike ExcelisFun Girvin in PDF and/or ePUB format, as well as other popular books in Computer Science & Desktop Applications. We have over one million books available in our catalogue for you to explore.

Information

Publisher
Holy Macro! Books
Year
2013
Print ISBN
9781615470075
eBook ISBN
9781615473298
Edition
1
Topic
Computer Science
Subtopic
Desktop Applications
Index
Computer Science
Chapter 1: Formula Basics
Excel Files
To follow along with the examples in this chapter, you can download the accompanying files, as explained in the Introduction.
What This Book Assumes
This book assumes that you already know how to create formulas. This chapter presents a condensed list of what you should know before starting on the examples in this book. If you feel that you need a review, check out the free Excel Basics Series at the excelisfun channel at YouTube (http://goo.gl/nFDAUp) or purchase the DVD Ctrl+Shift+Enter: Mastering Excel Array Formulas (a separate product from this book). The first video on this DVD contains 31 examples that demonstrate all the formula basics you need in order to succeed with this book.
Excel’s Golden Rule
Excel has a Golden Rule: If formula input data can change, put it in a cell and refer to it with cell references. If data will not change, you can hard code it into a formula. Examples of data that does not change are things like 24 hours in a day and the number 1 in a formula to calculate the complement of a tax rate.
The Golden Rule is the original idea that Dan Bricklin and Bob Frankston had in mind when they invented the first spreadsheet, VisiCalc. Formulas instantly update when you change the value in a cell; this is the leading advantage of formulas over other features in Excel, such as filters and PivotTables.
Formula Elements
To become skilled in creating array formulas, you need to understand the different elements that can go into formulas. Here is a list of the types of things you can put into formulas:
  • Equal sign (starts every formula as the first character in a cell)
  • Cell references (including defined names, sheet references, workbook references, and table nome...

Table of contents

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