M 365 Excel Worksheet Formulas & Models – Everything You Ever Wanted To Know - 365 MECS 03

This video is a comprehensive lesson in using Excel Worksheet functions and formulas to build Worksheet Solutions and Models.

Topics in video:
1. (00:00) Introduction
2. (01:12) List of Formula Elements
3. (01:54) Discuss Different Formula Types
4. (05:16) Examples of Modes of Formula Editing
5. (07:35) Examples of the five Calculation Formula Types
6. (08:43) Type #1: Relative and Absolute Cell References in Single Input-Output Formula
7. (12:40) When you must use ROUND function
8. (15:34) Use General Number Format as eraser to remove Number Formatting
9. (16:56) Type #2: Dynamic Spilled Array Formulas
10. (19:35) Spilled Range Operator for Dynamic Spilled Array Formulas
11. (20:07) Type #3: Excel Table Formulas
12. (25:47) Summary about SIOF, DSAF and TF formulas.
13. (26:34) Type #5: Aggregate Formulas
14. (26:43) Type #4: Scalar Array Formulas
15. (28:40) Style Formatting for Models
16. (33:13) Compare Number Formats: Accounting, Currency and Number
17. (38:00) Build Model #1: Sales & Expenses Budget
18. (49:40) Build Model #2: Markup On Cost Pricing Formulas
19. (54:32) Build Model #3: Date & Time Formulas & Number Formatting to calculate total days and total time for a work project
20. (01:02:24) Build Model #4: Largest Profit for Each Product. Learn about functions: UNIQUE, SORT and MAXIFS
21. (01:09:12) Look at Finance Cashflow Model to help pick investment
22. (01:09:42) Look at Analytics Simulation Model to product risk of new project
23. (01:10:05) Homework or practice problems
24. (01:10:21) Conclusion
25. (01:10:50) Closing and Video Links

#MikeGirvin
#excelisfun
#MikeexcelisfunGirvin
#Microsoft365Excel
#freeclass
#freecourse
#formulas
#functions
#DynamicSpilledArrayFormulas
#Microsoft365Formulas
#M365ExcelVideo3
#MECS3
#ComprehensiveFormulaLesson
#CompleteFormulaLesson
#ExcelFormulaClass
#LearnExcelFormulas
#SORTfunction
#UNIQUEfunction
#sumifs
#MAXIFS
#Excel’sGoldenRule
#excelisfunformulasfunctions
#BuildingModelsinExcel
#Excelsolutions
#freeclass
#excelformulas
#excelfunctions
#excelfunction
#excelmvp
#modleing
#modler
#formulas
#functions
#solution
#solutions

[Kennedybobieentertainment]

I didn't know more about the behaviors of Excel in difference modes but now i know, New excel calculation has saved lots from hitting the F4 to lock cell refences, Excel us really fun, Thanks Mark🙏🙏🙏🙏

[temidayoadebiyi]

Hi Mike. I hope u are feeling much better now. Thanks again for the upload. But don't forget to rest well... We love u Mike

[Vandalfoe]

You're so right! The implementation at 44:45 looks like the PERFECT use of spilled array formulas! I'm still having to relearn some of the basics, but it's part of what makes Excel so much fun!

[chrism]

The Master is back! Great video Mike!

[rachidachaib-jn]

Hi Mike I just want to say I love the way you are teaching really wonderful 👏

[canirmalchoudhary]

I was in thought that I know dynamic array but example shown here blew me. Now I am just thinking dynamic array within formula. This video is ultimate video on formula building. So much to learn from here. # MECS 🙌

[jvclfsm]

"Epic"? For lack of a better word! I learned so damn much! Now if I only had the 365 software to apply what I learned! I'm using Windows 7/Excel 2013 🙄. You should be Sainted!

[simoiyahector-morales]

It's like I learn all over again, love these nuggets

[Hey_Delight]

Just wanna say Hi Sir Mike.
Im still using 2007 but I love that you're doing 365 tutorials for viewers who are using one.
Have a great day Sir.🙂⭐💜💙🤎💚

[lucaviglio]

Finally i have some vacations and i can come back into studying...super tutorial as usual...so many tips....so many cues....thanks again Mike

[Kamalfayadh]

It's a brief on the historical progress, it shows where each person stopped in the learning curve 😎

[richardhay]

Great video!! BTW It makes very little difference when entering only a few time values as in this video, but for those who enter time values regularly or have to enter time values in bulk, data entry becomes much faster by taking advantage of the fact that in an AM/PM format AM is the default. So if you enter 10:00, when you press Enter it will record as 10:00 AM and 10:00 p will record as 10:00 PM. I would find it even more fun if keyboards had a colon on the number pad (and for DA a # key as well)!!

[jimbero]

Great learnings from the great lecturer. Thank you for sharing

[jimmyPOUFAJones]

Thank you! May not use a lot of this but really enjoy learning 365!

[mervilan]

You are a very very good teacher ... thank you.

[mbrown]

Welcome back!!!! Happy you are feeling better :)

[thinktoomuchb]

Thanks! Learning from your videos has really helped me stand out at my job. One question (obviously not within the the scope of this particular video): do the new dynamic array formulas require more RAM, but also have a speed and/or file size advantage over single cell formulas?

[MT]

Very useful video Mike! Really enjoy the use of the dynamic spilled array formulas which are really convenient. Thank you! :)

[PemberiSolusi]

very help me with this video, thank so much..

[juliomares]

Lets goo! I've been waiting on this one, thanks for dropping today!