   Introduction To Array Formulas and Array Functions
by
Video Lecture 32 of 50
Not yet rated
Views: 805 Lights Out New Window

### Lecture Description

Topics in this video:
1. Essential definition of array formula: operation on an array of items rather than single items. (00:30 min)
2. Example of operation on single items: subtracting 2 cells (00:52 min)
3. Example of operation on an array of items: Subtracting 2 ranges of cells (arrays) inside SUMPRODUCT function to calculate total profit (02:08 min)
4. SUM function number1 argument is not automatically programmed to handle array operations (we get a #VALUE! error) (03:14 min)
5. SUMPRODUCT array argument is programmed to handle array operations. SUMPRODUCT can add the resultant array that is produced from the subtraction array operation. Add the individual profit numbers. (03:38 min)
6. Example of operation on single items: 1 criteria entered into the criteria1 argument of the SUMIFS function (04:44 min)
7. Example of operation on an array of items: 2 criteria entered into the criteria1 argument of the SUMIFS function (function argument array operation) (05:19 min)
8. SUMPRODUCT array argument is programmed to handle array operations. SUMPRODUCT can add the resultant array that is produced from the SUMIFS function. Add the individual totals for each team member. (06:35 min)
9. Single cell SUMPRODUCT array formula to add the total Gross Profit (06:55 min)
10. What the array operation looks like (07:40 min)
11. Formula Evaluator to see how the array operation works (08:00 min)
12. Calculate max gross profit. MAX function with an array operation. (09:06 min)
13. Number 1 argument in MAX function will calculate array operation only if you use Ctrl + Shift + Enter (10:55 min)
14. Array Formula Curly Brackets (11:10 min)
15. No Ctrl + Shift + Enter = #VALUE! error (11:17 min)
16. AGGREGATE array argument is programmed to handle array operations. See how to use AGGREGATE for a MAX calculation (12:09 min)
17. AGGREGATE array argument is programmed to handle array operations. See how to use AGGREGATE for a MIN calculation (15:36 min)
18. How to think about filtering values in a formula (16:19 min)
19. MIN and IF functions together to make a conditional minimum calculation (17:38 min)
20. MAX and IF functions together to make a conditional minimum calculation (22:53 min)
21. Implicit Intersection causes incorrect answer, but not a #VALUE! error (23:43 min)
22. MODE.SNGL and IF functions together to make a conditional minimum calculation (24:47 min)
23. STDEV.S and IF functions together to make a conditional minimum calculation (25:36 min)
24. Array Functions (27:15 min)
25. TRANSPOSE Array Function (27:23 min)
26. FREQUENCY Array Function (29:09 min)
27. Summary (32:46 min)

### Course Description

This is the Highline Community College Class, Spreadsheet Construction taught by Michael Girvin: Basic To Advanced Excel. It contains 50 video lectures teaching the basics of Excel, as well as advanced concepts.

Be sure to download the workbook to follow along with the examples. They are found on the official course website.