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

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.

[Jojosmith]

Thank you so much for teaching us and make our life easier. Thumbs up as always. Already subscribed. Also, thanks for sharing your valuable video. Save us a lot of time

[shubhampawar]

Arrays formulas one of most difficult thing for me ...but you made it simple for me ....thanks a lot ❤️

[TurboTeen]

Still sticking to advance playlist and completing this playlist this month had so many problems to continue this excel series but still manage to continue thankyou very very much mike😀

[chantelhunsucker]

The attention to detail that you have put into every video workbook notes (next to the examples), plus the handouts for each video, truly reflects how much you care as a teacher. Thank you!!!

[nokibulalam]

May Allah bless you with all the good things for making these life-saving Excel videos.

[melissaramos]

Mike
I’m following your videos since the first one and I have already reviewed them countless times so that I do not lose anything of your teachings. I work daily with excel and it's amazing how much I've evolved since I follow your amazing presentation. Despite all the positive comments I read about your videos, I can not avoid to stress how much your work, enthusiasm and dedication should have already improved the professional life of all who follow your presentations and who work daily with this fantastic tool which is Excel. For your work and dedication, thank you very much.

João Ramos

[skillhub]

best excel channel I ever found on youtube. it's excel treasure. Hats off.

[nagizadeorxan]

This was another night that we were amazed by your great sharing, wish you all of the best!

[PieterSteens]

This was the most difficult subject I have encountered in your basic and advanced (up until now) excel series. I had to rewind a couple of times and really try to grasp what was happening. However I think I did a good job understanding what is happening under the hood of these array formulas. I will sleep on it for a night, then do the HW problems tomorrow. Another note: I really feel you can get creative with array formulas to solve problems that do not have a straightforward solution. This seems like the 'key' to more complex solutions to problems in Excel. Very interesting video as always and I thank you. On to video 6 and beyond!

[johnborg]

Always good to go back. Never considered boring to the slightest. Thanks mike

[PhilShnider]

Thank you for sharing your knowledge Free of charge with the world, this nearly 500++ videos would benefit, not only today but for many years to come.

[TheNabob]

I truly appreciate your videos. I have been a great fan of Excel since the beginning and am fairly proficient with it; but I always learn something new from your series. Thank you so much!

[sherryizzie]

Function argument array operation in Sumifs criteria1 argument, how come I did not discover this earlier! Thanks, Mike! I picked up ONE MORE new trick from your Excel video! Hurrah!!

[quangminh]

Awesome knowledge sharing, great effort as always. We can never thank you enough, Mr. Mike.

[Chamchijjige]

Watched. Refreshment tour. Thank you very much again.

[EricaDyson]

Totally agree. Fantabulous!! Thanks so much. Must admit that my head is spinning with arrays!

[tommyoneill]

Fantastic as always, I'm working through your videos step by step and learning so much.


Brilliant stuff Mike.

[ELGORNATIANNA]

I am discovering your channel today. I am impressed. Thank you.

[PedroCabraldaCamara]

Another amazing video!! I'm a lot less confused about array formulas. Tks a lot Mike

[GoQatari]

Hi Mike, I really thank you as I have learnt loads from you and some other excel tutors' YouTube videos when I only knew simple if commands only.

I have put together an array formula that is toooo long and can still be done better and simpler I believe. Almost on daily basis, I work with very long lists (1000+ lines) which have only one column. the cells in this single column are Alpha Numeric and are very unstructured and my job is to extract certain data which are not all properly tagged.

I would like your guidance on who can help me improve the formula to improve the quality of the extraction.

Appreciate your excellent videos and your kind guidance