Ctrl + Shift + Enter: Excel Array Formulas 15: General Guidelines For Array Formulas

EXCEL ARRAY FORMULAS WORK THE SAME IN ANY VERSION OF EXCEL!!!

This video covers General Rules For Array Formulas (This list is different than the book's list):
1. Array Formula Rule #1: Define an "Array":
2. Array Formula Rule #2: Types of Arrays in Excel.
3. Array Formula Rule #3: Define Array Formula.
4. Array Formula Rule #4: Entering Array Formulas into cells: Sometimes you must use Ctrl + Shift + Enter.
5. Array Formula Rule #5: What Ctrl + Shift + Enter and the curly brackets mean.
6. Array Formula Rule #6: What happens if you forget to use Ctrl + Shift + Enter?
7. Array Formula Rule #7: In some cases, helper column/cell solutions can be faster calculating than single cell Array Formula solutions.
8. Array Formula Rule #8: In some cases, Database functions are easier to construct and faster calculating than Array Formulas.
9. Array Formula Rule #9: In some cases, PivotTables can be easier to construct than Array Formulas.
10. Array Formula Rule #10: For lookup Array Formulas, sort the lookup column & use "Approximate Match" to decrease formula calculation time.
11. Array Formula Rule #11: If you are adding the result of an array calculation, SUMPRODUCT can usually help you avoid Ctrl + Shift + Enter.
12. Array Formula Rule #12: Syntax and rules for array constants.
13. Array Formula Rule #13: Non-Array Formulas can use array constants to save spreadsheet space.
14. Array Formula Rule #14: Array Functions guidelines.
15. Array Formula Rule #15: Array formulas entered into more than one cell using Ctrl + Shift + Enter act as a single unit.
16. Array Formula Rule #16: Selecting most efficient functions for Array Formula can reduced calculation time (may require timing).
17. Array Formula Rule #17: List of function arguments that can perform array calculations without Ctrl + Shift + Enter.
18. Array Formula Rule #18: range & criteria_range arguments in SUMIF, SUMIFS, COUNTIF and similar functions can't handle array calculations.
19. Array Formula Rule #19: INDEX & OFFSET functions can return a range of cells (a type of array).
20. Array Formula Rule #20: Boolean AND and OR Criteria guidelines.
21. Array Formula Rule #21: Converting TRUEs & FALSEs to 1s & 0s can be done with any math operation. Timing can determine the most efficient.
22. Array Formula Rule #21: Advantages, Disadvantages and important points to consider when creating Array Formulas.
THIS VIDEO SERIES AT YOUTUBE IS THE SAME AS THE DVD FROM EXCELISFUN. THESE VIDEOS ARE BEING GIVEN AWAY FOR FREE AT YOUTUBE. SUPPORT THE CAUSE BY GOING TO AMAZON AND BUYING THE BOOK.
EXCEL ARRAY FORMULAS WORK THE SAME IN ANY VERSION OF EXCEL!!!
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.

[excelisfun]

Ctrl + Shift + Enter: Excel Array Formulas 15: General Guidelines For Array Formulas.

At 01:15 minute mark, video shows how to print to get a handout of Array Formula Guidelines.

[pmsocho]

Great contribute to the "Excel world". Thanks for your time and work!

[warrenmcaleny]

Thanks for all the guidance over the years - awesome videos.