Download files here:
http://people.highline.edu/mgirvin/excelisfun
.htm
EXCEL ARRAY FORMULAS
WORK THE SAME IN ANY
VERSION OF EXCEL!!!
This video covers:
1. (00:34 min) Why use formulas to extract records from table, when there is the
Filter feature?
2. (02:16 min) Formulas to extract records often contain a large number of ranges & calculations & therefore may significantly increase spreadsheet calculation time.
3. (02:46 min) Filter for extracting records with AND
Criteria.
4. (04:50 min) Advanced Filter for extracting records with AND Criteria.
5. (06:35 min) Why formulas for extracting records are so complicated.
6. (07:25 min) Helper column non-Array
Formula for extracting records with AND Criteria.
7. (16:33 min)
Array formula using
INDEX &
SMALL functions for extracting records with AND Criteria.
(21:48 min) NEVER use IFERROR for these formulas!!!!
8. (24:24 min)
Let's remind ourselves about how the
Excel 2010 AGGREGATE
function works (not available in earlier versions).
9. (24:24 min) Array formula using INDEX & AGGREGATE functions for extracting records with AND Criteria.
10
. (30:39 min)
Timing huge data set for Helper Column, INDEX & AGGREGATE functions, INDEX & SMALL functions, and the use of IFERROR.
11. (32:41 min) Efficient & Inefficient use of IFERROR function.
12. (33:37 min) Display extracted records vertically using ROWS function or horizontally using
COLUMNS function.
13. (35:25 min) Filter for extracting records with OR Criteria.
14. (35:25 min) Advanced Filter for extracting records with OR Criteria.
15. (35:36 min)
Example 1: Non-Array Formula with helper column for extracting records with OR Criteria.
16. (39:14 min) Array formula for extracting records with OR Criteria (3 examples). Example2: AGGREGATE and Boolean. Example 3: AGGREGATE and
MATCH. Example 4: SMALL and Boolean.
17. (49:48 min) Timing formulas for extracting records with OR Criteria: Helper Column,
Boolean OR Calculation or MATCH function with
Exact Match?
18. (51:56 min) Array formula using INDEX & AGGREGATE functions for extracting records in a Two-Way
Lookup with OR and AND Criteria.
19
. (01:09:33 min)
Extract Names in
List 2 that are NOT in List 1: Helper Column.
20. (01:10:51 min) Extract Names in List 2 that are NOT in List 1:
2010 Array Formula.
21. (01:11:45 min) Extract Names in List 2 that are NOT in List 1:
2003 Array Formula
.
22. (01:12:27 min) Use Helper Column in
Data Extraction Area when you can't have one in Data Area.
23. For more examples of Data Extraction with Formulas see this excelisfun YouTube
Playlist: https://www.youtube.com/playlist?list=PL63A7644FE57C97F4
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!!!
Buy
Ctrl +
Shift +
Enter: Mastering
Excel Array Formulas DVD at A: http://www.amazon.com/Ctrl-shift-enter-Mastering-Formulas/dp/1615470085
Ctrl + Shift + Enter: Mastering Excel Array Formulas
Book: http://www.amazon.com/Ctrl-Shift-Enter-Efficient-Calculating/dp/1615470077
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.
Full playlist of videos at YouTube: http://www.youtube.com/playlist?list=PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci
- published: 29 Jan 2014
- views: 28019