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!!!!
"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!
@@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'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!
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 🙌
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 : )
@@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 :-)
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)!!
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.
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.
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?
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.
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
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
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.
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?
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..??
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)))
@@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.😊
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 : )
Hi, a question, in the microsoft 365 excel that I use, time is shown: ق.ظ instead of AM. I have changed the language and even the region, but it's still the same. how to fix this?
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.
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
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.
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)
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
@@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.
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.
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!!!!
The Master is back! Great video Mike!
Thanks, Teammate Chris M!!!!!
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!!!!
"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
Hi Mike I just want to say I love the way you are teaching really wonderful 👏
Welcome back!!!! Happy you are feeling better :)
Boomeranging back for more Excel fun ; )
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!
It's like I learn all over again, love these nuggets
Glad you like these formula nuggets!!!!
Thanks Mike. That was FUN!!
You are welcome, Formula Guy John : )
Thanks Mike for this EXCELlent video.
You are welcome, Fellow Teacher!!!
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
thank you very much for this material
You are 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 : )
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!!!
Great video Mike as usual thanks 😊
You are welcome, H T E A E.C : )
You are a very very good teacher ... thank you.
You are welcome!
Thank you!
You are welcome!!!
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!!!!!
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 : )
Great learnings from the great lecturer. Thank you for sharing
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
Very useful video Mike! Really enjoy the use of the dynamic spilled array formulas which are really convenient. Thank you! :)
You're very 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
Thank you! May not use a lot of this but really enjoy learning 365!
Glad you are enjoying it, Jimmy!!!
Boom!Truly Epic Super Fun Class...Thank You Mike :)
You are welcome, Biking Brother darryl!!!!
As usual, Amazing Video.............Thank´s Mike....................
Thanks, Teammate Fili!!!!
Been waiting for this video for a while now. Thank you for sharing. You are the best!
You are welcome for the share, Joshua!!!!
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 : ) : ) : )
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 😉
very help me with this video, thank so much..
You are welcome so much, DC C!!!!
It's a brief on the historical progress, it shows where each person stopped in the learning curve 😎
Nicely said, Kamal!!!!
Thank you very much.
You are welcome very much!!! Great to see you hitting the MECS class : ) : )
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!!!!!
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, Thanks
You are welcome, Hassan!!!!
Heyyy mike , hope you doing great..
Eagerly Waiting for next video..
Glad you like teh videos!!!!
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!!!!
Done!
Awesome!!! Did you practice all the examples in the workbook and review the pdf notes?
@@excelisfun Got ‘em!
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
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
Nice video wonderful video
Glad it is wonderful for you, Joynal!!
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.
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
1:00:47 why not change time format from 7:30 AM to 7:30?
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
You rock !
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.
@@TreyMauleon You are welcome!
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
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?
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.😊
Sooo trueeee
Hope you like this vid!!!
Hi mike
I have to ask why my Excel workbook works very slowly i am doinflg work on he workbook you provide usi the description
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
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!!!!!
Hi, a question, in the microsoft 365 excel that I use, time is shown: ق.ظ instead of AM. I have changed the language and even the region, but it's still the same. how to fix this?
Nice
Glad it is nice for you : ) : ) : )
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
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 : )
Hii , how to solve the last problem, if we are not using 365?
Let's go
Yes: it's ready for you to go have fun and gain power with Excel 365!!!
Hello, angry rabbit cursor doesn't appear while trying the dynamic spill formula with my Office 2019. What can i do about it? thanks
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.
Amazine
Glad you like it, Jone!!!
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)
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 🙏👍
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
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 : )
Yo mike (M9:M13*N6) this is my command but it only calculating m9*n6 why ?.....!
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 : )