Kevin's posts

Post has attachment

Public

Are you wasting time endlessly scrolling an Excel data validation list? Here are some solutions https://youtu.be/8-MpnD_MDeM

Post has attachment

Public

Unstack data challenge with Oz du Soleil and Mike Girvin!

Post has shared content

Public

Learn Microsoft Excel array formulas!

Blue Cover (3rd Printing) Ctrl + Shift + Enter: Mastering Excel Array Formulas Book at mrexcel.com

Post has attachment

Public

Fun creating more solutions for Mike Girvin's challenge. https://youtu.be/gL0ony5zpK0

Post has attachment

Post has attachment

Post has attachment

Post has attachment

Public

Learn how to loop in VBA

Post has shared content

Great video about arrays by Mike Girvin

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

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm

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

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm

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

Post has attachment

This was a good challenge: Insert rows and cascade number below without using Excel functions or VBA. https://youtu.be/OgVdf_W_eGg

Wait while more posts are being loaded