Loading...

Highline Excel 2016 Class 05: Excel Array Formulas: Comprehensive Lessons: 12 Examples

38,376 views

Loading...

Loading...

Transcript

The interactive transcript could not be loaded.

Loading...

Rating is available when the video has been rented.
This feature is not available right now. Please try again later.
Published on May 1, 2016

Download Files: https://people.highline.edu/mgirvin/A...
In this video learn about:
1. (00:04) Download Files
2. (00:24) Aggregate vs. Array Calculations & Define Array
3. (02:55) Define Array Formulas
4. (05:49) Example of non-array formula. Standard Formula: Formulas that calculate a single answer. Goal: Calculate Total Expense and Total Net Income.
5. (06:43) Array Formula 1: Math Array Operation. Goal: Calculate Total Expense for each month without making calculations in many cells (we don’t need all the individual numbers). Main reason we use Array Formulas: To create compact solution to do all the intermediate steps in a single cell.
6. (10:00) How to decide whether or not you must use Ctrl + Shift + Enter to put the array formula into the cell.
7. (12:06) Implicit Intersection.
8. (14:49) SUMPRODUCT function to house an array calculation to avoid using Ctrl + Shift + Enter.
9. (16:46) Array Formula 2: Math Array Operations. Goal: Calculate Overall Total Expense in a single cell.
10. (19:34) Array Formula 3: Math Array Operations. Goal: Calculate Overall Net Income in a single cell.
11. (20:48) Formula 4: Use more than one argument in SUMPRODUCT. Goal: Calculate total Bank Deposit. Learn about SUMPRODUCT and discussion about size and dimensions of arrays in array calculations.
12. (25:06) Array Formula 5: Function Argument Array Operation in k argument. Goal: Add 3 biggest and 3 smallest. Use Array Constant to avoid Ctrl + Shift + Enter.
13. (28:32) Array Formula 6: Function Argument Array Operation in criteria1 argument. Goal: Add Sales for each Sales Team. Example of using an Array Formula to create a MUCH smaller formula than non-Array Formula alternatives.
14. No Example 7
15. (32:16) Array Formula 8: Function Argument Array Operation in criteria1 argument. Goal: Score Myers-Briggs Test in Human Resource Department.
16. (35:21) Array Formula 9: Function Argument Array Operation in criteria1 argument. Join Array Operation. Goal: Score Myers-Briggs Test in Human Resource Department.
17. (37:15) Array Formula 10: IF function array formula to filter out values for an aggregate function. Goal: Calculate Average Gross Profit for Each Sales Rep. Learn about how to use IF Function to filter out values that we don’t want for our aggregate calculation.
18. (38:12) List of Functions that can NEVER do Array Calculations.
19. (43:11) Array Formula 11: Built-in Array Function: TRANSPOSE. Goal: Transpose rows and columns so we have Assumption Table that allows Mixed Cell References. Example of Array Formula where we enter multiple values as an array into multiple cells simultaneously.
20. (45:42) Array Formula 12: Built-in Array Function: FREQUENCY. Goal: Count how many sales are in each category.
21. (50:31) Summary

Learn Array Formulas from Beginning to End.

Loading...

Advertisement

to add this to Watch Later

Add to

Loading playlists...