M 365 Excel Worksheet Formulas & Models - Everything You Ever Wanted To Know - 365 MECS 03
Вставка
- Опубліковано 27 чер 2024
- Download Excel File: excelisfun.net/files/03-M365E... Download PDF File: excelisfun.net/files/03-M365E...
This video is a comprehensive lesson in using Excel Worksheet functions and formulas to build Worksheet Solutions and Models.
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: • Microsoft 365 Excel & ...
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
Song in video: Rock Intro 3 by Audionautix is licensed under a Creative Commons Attribution 4.0 license. creativecommons.org/licenses/... . Artist: audionautix.com/
#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
58:54 Latest Date - Early Date. + 1
I have done 1,000 of videos (including many # project days calcs) and I can not believe that I missed that. But luckily, I am on a good Team and you got my back, Fili!!! Thank you. Go Team!!!!
In the old UA-cam, we used to be able to add annotations. But not now. Too bad, because I would add a note about your insight : )
I understand what you are saying since if you started and ended on the same day, it would show 0 days, but there are still 7.5 hours.
@@bagnon That was my thought as well. It can only be 1 day when at lest 24h are used.
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
Thanks for the love, Temidayo!!!!
The Master is back! Great video Mike!
Thanks, Teammate Chris M!!!!!
"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!
Thank you for the sainthood, R!!!!! I hope you will get M 365 soon, because it changes everything : ) : ) : )
@@excelisfun it makes so much sense that you have to wonder why did it take all these years? The dynamic array spill is like.. everything we've wanted. Well just about...
@@user-jv1cl2fs6m All I know2 is that all entities that are not willing to pay for a subscription are not doing the true cost benefit analysis. Anyone making calculations and doing data analysis, which is almost all entities in world is likely to benefit from M 365.
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🙏🙏🙏🙏
You are welcome, Kennedy!!! - Mike "excelisfun" Girvin
Thanks!
Thank-you for this video.
Thank you very much for helping this channel go, Santosh!!!!! : ) : ) : ) : )
@@msantosh1220 You are welcome, Teammate!!!!!!
I feel so proud when you call me Teammate😇🥰
@@msantosh1220 Me too : ) Go Team!!!!
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!
These new formulas just make this soooooo much easier : ) Lucky for us!
Hi Mike I just want to say I love the way you are teaching really wonderful 👏
Thanks Mike for this EXCELlent video.
You are welcome, Fellow Teacher!!!
Welcome back!!!! Happy you are feeling better :)
Boomeranging back for more Excel fun ; )
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.🙂⭐💜💙🤎💚
I have full classes for 2007. However, the better classes and videos are in my 2016 classes, which are close enough to 2007. What version would you like to study? 2007, 2010, 2013 0r 2016? I have playlist for each : )
Thanks Mike. That was FUN!!
You are welcome, Formula Guy John : )
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
It's like I learn all over again, love these nuggets
Glad you like these formula nuggets!!!!
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 🙌
So glad you like it!!!! Tell all your friends and colleagues so more people can have power and fun with formulas ; ) MECS!!!!!
Lets goo! I've been waiting on this one, thanks for dropping today!
Thanks for waiting! Dropping and ready for you to have fun with Excel 365, Julio!!!
very help me with this video, thank so much..
You are welcome so much, DC C!!!!
Thank you! May not use a lot of this but really enjoy learning 365!
Glad you are enjoying it, Jimmy!!!
It's a brief on the historical progress, it shows where each person stopped in the learning curve 😎
Nicely said, Kamal!!!!
Great video Mike as usual thanks 😊
You are welcome, H T E A E.C : )
Been waiting for this video for a while now. Thank you for sharing. You are the best!
You are welcome for the share, Joshua!!!!
Great learnings from the great lecturer. Thank you for sharing
Boom!Truly Epic Super Fun Class...Thank You Mike :)
You are welcome, Biking Brother darryl!!!!
Thank you!
You are welcome!!!
At last the silence is broken and we have a 60+ minutes video. Learning never stops with your video ❤️.
60+ minutes of condensed formula fun!!!!!
Yes sir, 60+ full minutes of condensed formula work
thank you very much for this material
You are welcome!!!
As usual, Amazing Video.............Thank´s Mike....................
Thanks, Teammate Fili!!!!
Very useful video Mike! Really enjoy the use of the dynamic spilled array formulas which are really convenient. Thank you! :)
You're very welcome!
This is great, Teammate! even more fun working on the Excel File with all these cool formula :):):):):):):)
Yah!!!! Teammate : ) : ) : ) : ) I miss you at work...
@@excelisfun I will see you soon :)
@@marykiandokajoka8703 I can't wait : ) : )
Make some more videos, Mary!
@@richardhay645 I hope so too : )
great, Thanks
You are welcome, Hassan!!!!
Thank you very much.
You are welcome very much!!! Great to see you hitting the MECS class : ) : )
Nice presentation mike. Thanks for all these explanation :-)
You are welcome for the presentations, Mohamed. Thanks for watching even though you are really smart with Excel and know all this ; )
@@excelisfun thanks big boss. You make everyone love excel when you make a presentation. You have a great methodology when you show something. Other thing I have learn a lot lot and lot from u :-)
@@mohamedchakroun4973 I am happy my methodology can help you learn a lot a lot a lot : ) : ) : )
You are a very very good teacher ... thank you.
You are welcome!
Valuable video . Thanks for uploading. So many things i have learned today.
I love to hear that you are learning many new things - that makes it a great day!!!!
@@excelisfun My resolution to continue to watch whole segment
@@RA-rh5lb The MECS class has it all - and you can come back as often as you want. Have you downloaded files to follow along?
@@excelisfun yes. I did. Thanks.. I might need to come back when i practice .. Great videos
You rock !
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)!!
Very awesome tips, Richard!!!!! Thanks for helping the Team : )
you may use auto-correct option to assign ".." (double dot) > ":" (Colon) to make your life easy. Similar for "#"
@@viralshah3649 Great tip!!! It is funny, I use auto correct all the time in Word, but I never think to use it in Excel...
@@viralshah3649 just added .. to return : to my autocorrect. Good hack. Thanks!
@@richardhay645 as Mike says. We are all a team 😉
When doing the formula for sum table mines show this =SUM(TaxTable[[#Headers];[Tax Amount]]). How do we get rid of the #Headers
s
I am not sure how it got there. Maybe clicked too high of field name? Try making sure you see just downward pointing black arrow. Post back and let me know if that works : )
@@excelisfun Thanks it worked.
@@ibrahimbagas239 Yes!!!!!
Heyyy mike , hope you doing great..
Eagerly Waiting for next video..
Glad you like teh videos!!!!
At 41:26, when you are filling the months over, what are you hitting on the keyboard to go from the highlighted cells to the cell just to the right of April? Also, when I do type "Total", the enter key does not take me back to the beginning under "Amounts in $". What are hitting there to accomplish that? Thank you!
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?
As far as I know, there should be no difference between the same formula that is single input-output and dynamic spilled array. The same number of calcs still have to be made in both.
Very true 👍
Great video about various types of formulas. 🙂👻
Your ghost was at 18:05 ? : ) : ) : ) : )
@@excelisfun Yes. I saw the cute ghost. 🙂
@@kiwikiow I keep the Ghost around because your comment a few years back : ) : ) : ) The Ghost is good to have around : )
@@excelisfun Yes, it is. It's a good visual appeared when the content is about ghost formula. Thanks for keeping the ghost 👻
@@kiwikiow Go Team!!!!!
Nice video wonderful video
Glad it is wonderful for you, Joynal!!
Done!
Awesome!!! Did you practice all the examples in the workbook and review the pdf notes?
@@excelisfun Got ‘em!
Thank you very much for all your videos on power query and power pivot
Can I have a solution as to how to fetch price in sales table based on three fields in product table namely date, product and customer group
Yes, I have a video of three value lookup: ua-cam.com/video/6swxi4p5M-w/v-deo.html or ua-cam.com/video/2kvPdv_nvbM/v-deo.html
Thank you very much for your quick reply
Great video again! And as always clearly explained. I have one question. 9 out of 10 times we want to stay in the same cell. Why are you using CTRL + Enter while we can set this as a default by unchecking the box through File >> Options >> Advanced >> unchecking "After pressing Enter, move selection"?
Thanks for the hot tip : ) I have always kept the default because I make videos. Also, I use Enter and Ctrl + Enter about the same, so I just trained myself decades ago to just do this way.
@@excelisfun Thanks Mike. I get that. Everyone has their own way of working.
Sooo trueeee
Hope you like this vid!!!
If the work was performed on 7/13/22, plus every day through and including 7/27/22, that is actually 15 days. Is there a way to write the formula to capture this?
Latest Date - Early Date. + 1
In homework #3 the word problem lists Net Sales for 12 months. Shouldn't this be Gross Sales (or Sales) since the Net Sale(Profit) is what we are solving for?
Nice
Glad it is nice for you : ) : ) : )
Hi Mike, Dhanush from India... I'm unable to get the copy method which you did in 42:11. its showing me value error.. so what do i do now
Great video. Is there a way to use the AGGREGATE function rather than the MAXIFS? I don't have the new version of Excel thats why. Thank you!
If you do not have MAXIFS, this is not the video class for you because MAXIFS came to Excel 4 versions ago, 7 years ago. None of the spilled array formulas in this class (about 75% of what we do in this class). Here is the class that you should use: ua-cam.com/play/PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k.html
Here is a video about Aggregate for MAXIFS: ua-cam.com/video/I85MA02C-GM/v-deo.html
@@excelisfun Thank you so much for linking another video. Your videos are great help to many, including me.
@@user-vt7yc7pr3l You are welcome!
Thank for the tutorial.. while practicing the dynamic spilled array formula mine didn't spill, I tried to manually copy but it was showing me value error .. please what could be the problem
You don't have Microsoft 365 Excel. When this version came out, they changed the entire calculation engine inside Excel. Older versions do not have this. This class teaches M 365 Excel. I have many other videos and classes that teach the older versions.
Ok thank you sir I will check it out
at the 23:30 mark, for the sum pull down on the table, is there something in settings that needs to be on for the pulldown to show, because i don't get the pull down, when i do the calculation above? I get a #Value! error that appears, where the sum total should go.
same error, need to dig further
Let's go
Yes: it's ready for you to go have fun and gain power with Excel 365!!!
1:00:47 why not change time format from 7:30 AM to 7:30?
Can we make recursiver array formulas..like:
Output 1 = Value 1 * multiplier
Output 2 = Output 1 * multiplier
Output 3 = Output 2 * multiplier and so on..??
Yes, but I do not know how to do it. It involves the LAMBDA function. Try searching for recursive LAMBDA.
For a nr. of iterations "n" ("and so on" should have a determined nr. of iterations or other exit condition that will trigger the "loop" calling of the recursive function to exit)
a: initial value
m: multiplier
n: nr. of iterations
function MLT(a,m,n)=
=LAMBDA(a,m,n,IF(n=0,a,MLT(a*m,m,n-1)))
- or if you have M365 beta insider active, without recursive
=LAMBDA(a,m,n,REDUCE(a,SEQUENCE(n),LAMBDA(v,i,v*m)))
- or if you want to list all iterations
=LAMBDA(a,m,n,SCAN(a,SEQUENCE(n),LAMBDA(v,i,v*m)))
@@excelisfun Okaay Thanks!!
@@Excelambda Thanks a lot!,! You already knew lambda is gonna come in excel. right?
@@rehanshah2091 You're welcome. 🙏
Actually, I took this name after lambda emerged😉. YT updated the name for posts I did before lambda under a different name, so looks like I have anticipated it, but was not the case.😊
Hello Mike, hope you had a good vacation, I'm afrid this isn't linked to to current post but regards your question on power bi website , DAX Calculated Column Order of Multiplying Difference Error,
I'm still struggling to find a answer to this question, have you got one?
Richard.
Yes. Here it is: MSPTDA 24: DAX Multiply / Divide Data Types Discrepancies Solved! ua-cam.com/video/6420PcTGBv8/v-deo.html
BTW, have you seen the MSPTDA (Advanced Data Analysis) class? It is the best free Microsoft Tools class ever posted on the internet:
ua-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html
The video I posted for you about multiplication is #24 in this class
Hello, angry rabbit cursor doesn't appear while trying the dynamic spill formula with my Office 2019. What can i do about it? thanks
Amazine
Glad you like it, Jone!!!
amazing. new video. what is MECS abbreviate, sir ?
00:21
The 00:21 section of the introduction of the videos shows what MECS means : )
MECS = Microsoft 365 Excel Complete Story!!!!
@@excelisfun MECS stands for Mike Excel's Coolest Scientist ✌😉
Good to see you back in shape!!
@@Excelambda MECS lol
I would like to ask if there is a place I can download data for practice
All practice files and data files are in the Show More link below all 3,500 videos that I have posted over that last 15 years : )
After, I can't say how many years, I finally get the Angry Rabbit comment!!! (Cross Hare) He He He !!
Yes, those angry bunnies lol If you can have fun with Excel, the power is even greater ; )
🐰
@@Vandalfoe Bun bun!!!!
On my excel the dynamic spilled array formula is not working.. whats the problem??
Hello :) Please advise I am typing this in : =ROUND(B9*$C$6,2) and I get message that there is a problem with the formula - I should type '1+1 Is it a different excel version?
The message box that says there is a problem is almost always wrong. I never follow that dialog box advice. Your formula looks OK to me: =ROUND(B9*$C$6,2)
Hii , how to solve the last problem, if we are not using 365?
Hey! Mr.Excelles fun,plz can u make this video in Hindi language? This video is very important for me,I had seen and search fully Explained excel video but I don't got it.
Sirji... Hope you are fine now🙏
I am still a bit slow from covid, but ready to help the Team have fun with Excel!!!
@@excelisfun THAT'S THE FUN WITH EXCEL...😃 TAKE CARE SIRJI... GET WELL SOON 🙏👍
sirin hamza fruity edition doesn’t have all the plugins or samples and it can’t record content either
Hopefully you will find benefit in this video : )
In excel worksheet, whenever i enter dynamic spilled array formula,it only give answer to the first cell and all other cells are empty. can someone give me a solution.
PLEASE
What version of Excel are you using?
2019
@@Aravinthan-tp7lq almost none of the content of this class works in Excel 2019. Microsoft changes EVERYTHING in the newer Microsoft 365 Excel. Here are the two classes if you do not have the latest version:
First Class: ua-cam.com/users/playlist?app=desktop&list=PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k
Second Class: ua-cam.com/play/PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw.html
@@Aravinthan-tp7lq BTW, Microsoft 365 Excel is the only version that has everything. Maybe you are stuck at a business or entity like me, that still uses Excel 2019, which is too bad for us employees because Microsoft 365 Excel is so much better. If only our employers understood that the extra money spent is worth the extra efficiency and benefits. If you can switch as soon as you can to Microsoft 365 Excel - it is the only app that matters.
@@excelisfun thank u so much
is there a way I can message you privately, to see if you can help me with something that has been kicking my ass?
I am not am to do consulting at this time. THE best Excel question site is: mrexcel.com/board
@@excelisfun Thank you
@@Marv947 : )
Girvin 1 COVID 0 !?
The video was mostly done before I got covid, then I tried to work on it while I had covid, and it was sssslllooowww... But now I got it done : )
Nah literally...
I hope you like the video : )