Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516)

Подписаться 1 млн

Просмотров 87 тыс.

50% 1

Download Excel START File: people.highlin...
Entire page with all Excel Files for All Videos: people.highline...
This video is a comprehensive video about the new Office 365 Dynamic Array Formulas, Array Functions and Excel’s Calculation engine.
Topics:
1. (00:06) Introduction to the new Excel Calculation Engine and Array Formulas in Excel
2. (05:53) OR Logical Test AVERAGE Array Formula. Delivers a single Answer. No Ctrl + Shift + Enter
3. (07:22) How Old Single Cell Array Formula Behaved
4. (08:20) How New Calc Engine Avoids Trouble with Array Formulas
5. (09:08) FREQUENCY Function
6. (09:32) How Old FREQUENCY Function Worked
7. (10:00) FREQUENCY Function. New Calc Engine. Spill Automatically
8. (10:32) Where Spilled Array Formula Lives. Refer to Spilled Array with F40#
9. (12:45) Spill Error
10. (13:20) Standard Deviation. Delivers a single Answer. No Ctrl + Shift + Enter
11. (13:45) OR Logical Test Adding formula with SUMIFS & SUM Function, rather than SUMPRODUCT
12. (15:29) SUMIFS and Function Argument Array Operation will Spill
13. (17:00) Array Formula to create a Formula Report that is EAISER than using a PivotTable
14. (17:51) Unique List using UNIQUE Function
15. (18:18) Distinct List using UNIQUE
16. (20:18) Unique List and FILTER to avoid zeroes
17. (21:22) Unique Count Formula using COUNTA, UNIQUE and OR
18. (22:37) FILTER Function as Lookup with One Lookup Value & Return Multiple Items in Single Column
19. (24:00) FILTER Function as Lookup with One Lookup Value & Return Multiple Items in Multiple Columns
20. (24:22) FILTER Function as Lookup with Multiple Lookup Values & Return Multiple Items (Boolean Logical Test)
21. (24:44) #CALC! Error and the third argument in FILTER
22. (26:08) SORT Function to sort a filtered list
23. (27:50) Extract Sorted & Unique List from Mixed Data using SORT and UNIQUE
24. (28:38) Extract Unique & Sorted List of Mixed Data in Single Cell using TEXTJOIN, SORT and UNIQUE
25. (29:43) Spill Direct for Dynamic Arrays using Array Syntax
26. (30:50) Variable Length Spilled Arrays using LARGE & SEQUENCE Function
27. (32:00) SEQUENCY Function for incrementing Numbers in cells
28. (32:10) SEQUENCY Function for incrementing Stepped Numbers in cells
29. (32:28) MID, LEN and SEQUENCE to extract characters from a cell in a Row
30. (33:26) MID, LEN, TRANSPOSE and SEQUENCE to extract characters from a cell in a Column
31. (33:40) Dynamic PivotTable using SUMIFS and a number of new Array Functions
32. (35:15) SINGLE Function and Implicit Intersection
33. (37:45) Some Functions still will NOT make Array Calculations, like SUMIFS, COUNTIFS and AVERGAEIFS
34. (38:14) RANDARRAY Function
35. (39:01) Summary

Опубликовано:

28 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 324

@excelisfun 5 лет назад

The new Excel Calculation Engine, Spilled Arrays and the new Array Functions are pure Excel Magic! Best invention since... 2013 Power Query,,, 2009 Power Pivot and Columnar Database... 1990s PivotTable (borrowed from Lotus Improv)!!!! What do you think?

@powershah 5 лет назад

Are these functions only available for O365 or it will also be included in other Excel 2019 versions? Kindly reply. Thanks.

@excelisfun 5 лет назад

@@powershah Microsoft says that they are only in Office 365 and NOT in Excel 2019.

@powershah 5 лет назад

@@excelisfun it means that any O365 Excel file having these array functions will not be work if we open it in Excel 2016 or 2019 versions (other than O365) ? I tried your Excel magic trick file # 1520 in Excel 2016 prof. Version and these functions are not working.

@excelisfun 5 лет назад

@@powershah Yes, they do not work in earlier versions. Here is a video I made in this topic: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-nkXh5OFKeXg.html

@ExcelInstructor 3 года назад

@@excelisfun 22:23 and here i thought ur gonna subtract countblanks()...

@joshuamanampiu6489 2 года назад

Just got 365. Thanks for the tutorial. You are the best.

@excelisfun 2 года назад

You are welcome, Joshua!!!!

@edge5817 4 года назад

FINALLY MIKE... the new calculation engine is live to all office 365... LIFE IS NOW EASY! Thank you for all the things I've learned from you Mike!

@bijoysaraf650 6 лет назад

Outstanding feature in Excel! More importantly, you are an awesome teacher. Way to go Mike! Keep the videos coming. I have learnt quite a lot from your book CSE. Thank you for sharing.

@excelisfun 6 лет назад

You are welcome! Thanks for the support with buying the book "Enter" ; ) Thank you for your kind words and support, Bijoy!!!!!!

@martinsefelin4479 3 года назад

You’re an absolute King in my Excel multiverse! Thank you for excel-n-t vids!

@excelisfun 3 года назад

You are welcome for the EXCELlent videos, Martin!

@michaelbroyles3709 5 лет назад

The only thing more amazing than Excel's new functionality is Mike Girvin's ability to teach!

@deepakagrawal465 6 лет назад

Superb video, Mike. Loved watching it!!! Thanks.

@excelisfun 6 лет назад

Glad you loved it, Deepak!!! Thanks for watching and keep watching because I have more videos coming out over the next week about more amazing implications for the new engine : ) Thank you very much for the support with your comment, Thumbs Up and Sub : )

@wynhopkins4023 6 лет назад

Thanks for sharing that thorough insight into arrays

@excelisfun 6 лет назад

You are welcome, Excel Teammate Wyn!!!! Together we help to make the world a better place and (and more fun) with Online Excel Tips! And... This Array Things, and really it is the Excel Calc Engine thing, is way more profound than any of us can imagine. In the next week I will post more short tips of just crazy cool things that are possible, that we just never could really do before. It is going to get fun!!!

@spilledgraphics 4 года назад

An Array of TURBULENCE of Enlightenment ! ....woah ... Mike !!!

@excelisfun 4 года назад

You didn't watch this until now!?!?! And since this video i have made over 50 other Spilled Dynamic Array Formulas videos... Just WAY too much Fun with this new ability : ) : )

@spilledgraphics 4 года назад

@@excelisfun Truth be Told, I was watching everybody, talking it about it, but at the time, my clients didn´t have the proper Excel versions, and I was very scared I would end up very frustated that I cannot apply THIS magical abilities of Spilled arrays ... now many of them are adopting Office 365 !!! I can´t believe any of this AWESOMENESS !!!!

@excelisfun 4 года назад

@@spilledgraphics Me too. I can not believe all this AWESOMENESS!!!!! To transformative : ) : )

@spilledgraphics 4 года назад

@ExcelIsFun MASSIVELY transformative Mike !! Go Team !!!

@ataurrehman483 6 лет назад

You are really brilliant Prof.

@excelisfun 6 лет назад

Thank you for your kind words! Glad you like the video - thanks for your support : )

@qbamc 6 лет назад

Thank you Mike. I’ve learned a lot friends you over the years

@excelisfun 6 лет назад

Glad you learned a lot over the years, Wilfred Lopez!!!!

@davebowman5392 6 лет назад

So, I'll finally be switching to Office 365 then. A large company I do some training for in the UK still uses 2010, I bet they won't even be aware of the new features. Thanks as always Mike.

@excelisfun 6 лет назад

I would bet that they are not aware either. But now, with all that Power Query can do, Power Pivot as Default, Dynamic Arrays, New Calculation engine, TEXTJOIN, MAXIFS and so much more, we all have to get Office 365. Thanks as always, Dave, for your support : )

@manojsrikanth8663 4 года назад

Sir.. You are Hero of Excel.. 🙏🙏🙏☺️☺️

@excelisfun 4 года назад

Thanks, Manoj!!! Thanks for your support on each video with those comments, thumbs ups and of course your Sub : )

@manojsrikanth8663 4 года назад

@@excelisfun I am learning Excel from your😊😊 channel.. Hope one day I will become like you in Excel.. Thanks for your effort.. 👏👏💐💐💐

@excelisfun 4 года назад

@@manojsrikanth8663 Even better: you will become an Excel Master just like you : )

@LuciusSullaFelix 4 года назад

I saw this video when it first came out back in 2018 and have been chomping at the bit to try it out; however, my computer is an SOE corporate build where I do not have admin rights. The official office 365 build that we have only got updated today to allow these new functions. Will need to watch the video again so that I can actually use these new array functions.

@excelisfun 4 года назад

I am glad that you finally got them!!!

@LeilaGharani 6 лет назад

Major game-changer! Love the examples. We can all be Bill S. now :)

@excelisfun 6 лет назад

Love it!!!! Game Changer to the MAX!!!! I just hope all of our Teammates in the Excel World can get Office 365 soon, and that Microsoft will send out the new Excel Calculation Engine sooner than later : ) Thanks for your support, Teammate Leila : )

@johng5295 4 года назад

Thanks in a million. Second to NONE.

@excelisfun 4 года назад

You are welcome a million : )

@amit12000 6 лет назад

Great , it requested to make video all new development made in excel 2018

@excelisfun 6 лет назад

This video shows most all the changes for Excel Dynamic Arrays! The MSPTDA series shows all the new Power Query and Power Pivot featues! Together that is a LOT of new features in Office 365 Excel!!!! Thank you very much for your Support, Amit!!!

@amit12000 6 лет назад

@@excelisfun ok i will see all thank Mike

@excelisfun 6 лет назад

You are welcome, Amit!

@sandip_bettereveryday 6 лет назад

Pivot Table created through Dynamic Array functions is awesome! Can we have multiple levels of rows and columns here? Can we also allow users to choose fields to be used in rows and columns? How will charts react to dynamic array functions?? I would love to try all these, but my Office 365 version is yet to get this update.

@excelisfun 6 лет назад

Yes, it is possible... But we would have to get tricky to have multiple levels and Slicer like criteria. I will make another video soon - it will just be more and more fun figuring out the combinations : ) : )

@excelisfun 6 лет назад

We can definitely create a dynami PivotTable where we can let the user select columns in the Cross Tabulated Report, like: Row Header Formula: =SORT(UNIQUE(INDEX(fRevenue,,MATCH(I3,fRevenue[#Headers],0)))) Column Header: =TRANSPOSE(SORT(UNIQUE(INDEX(fRevenue,,MATCH(I4,fRevenue[#Headers],0))))) Values Area: =SUMIFS(fRevenue[Revenue],INDEX(fRevenue,,MATCH(I3,fRevenue[#Headers],0)),H7#,INDEX(fRevenue,,MATCH(I4,fRevenue[#Headers],0)),I6#) I am not sure about the nested levels yet...

@sandip_bettereveryday 6 лет назад

@@excelisfun Thanks for the reply. Will surely try out when I get the update. Did you try dynamic array with charts? Will charts accept A1# as a range?

@excelisfun 6 лет назад

Looks like Cell# does not work in Chart dialog box!?!?!?! It looks like if we wanted the chart to be reading the dynamic Arrays, we'd have to use Names or Dynamic Range Formulas...

@excelisfun 6 лет назад

Wow - it looks like if we create a Defined Name that points to Cell# (Dynamic Array) the Defined Name works in the cells, but NOT in Chart Dialog Box!?!?!

@arvindr4258 5 лет назад

This are fantastic features! Very well explained ,Thank you!

@excelisfun 5 лет назад

You are welcome, Arvind R!!!

@chrism9037 5 лет назад

Amazing Mike!! Keep the coming

@excelisfun 5 лет назад

Thanks for watching this again and commenting again, Chris!!!!!

@xandesk 6 лет назад

Wow It's amazing! Thanks Mike! I'm anxious to use these formulas :D

@sjn7220 6 лет назад

Wow, mind blown, thanks! Too bad my company uses the perpetual license and not office 365, and is usually a version or two behind anyway ☹️.

@excelisfun 6 лет назад

Yes, my mind is also blow - blown with happiness about these new Dynamic Arrays! I am sorry about your work, but sooner or later they will have to get this because there are just too many features that can help the business!!! Thank you for your support, sjn 72 : )

@philipbonet7939 6 лет назад

Great video! As always, really easy to understand from the beginners to the pros. I can’t wait to have that on Office 365! Any ideas when that would be?? Also, quick question here. Would the arrays formulas also spill the format of the first cell? Let’s say, if you have blue font on F4, would the blue font goes on all cells of F4#? Thanks!

@excelisfun 6 лет назад

It will only be in Office 365. MS says that all versions of Office 365 should have it in a few months, maybe early next year.

@alexandreletourneux9581 5 лет назад

Awesome..! Thanks for your work. In my company we use Microsoft Office 365 ProPlus (current version 1812) but still does not include these features.. hope it will come soon.

@excelisfun 5 лет назад

Yes, MS says in the next few months. I know for me, the more I use these new functions and Calc Engine capabilities in so many helpful ways. Thanks for your support with your comment, Thumbs Up and Sub : )

@wmfexcel 6 лет назад

Really GREAT!!!! They make our Excel life much more easier IF everyone is using Office 365. :P I guess MS has to do more promotion to corporate world to increase the penetration of 365 in workplace. Otherwise all these great functions will be "under-used"... One question though: Are these Dynamic Array function volatile ?

@excelisfun 6 лет назад

I totally agree with you about this: Microsoft is heavily enticing all of us to move the Office 365 because there are just too many helpful and efficient things that we all need, BUT... There is still impediments to large organizations adopting Office 365. I hope Microsoft fixes this. I know at my institution, the shared computer labs can't allow everyone to have Office 365, but all individuals have a free Office 365 from the institution that individuals can install on their personal computer. So until Microsoft can effectively and efficiently allow all users to have Office 365, we will be stuck in a less than efficient mode : (

@excelisfun 6 лет назад

I do not know if 7 new Array Functions are "volatile" in the old sense where every action in the spreadsheet causes a recalculation (like INDIRECT and TODAY), but there are at least volatile in that if the range they are pointing to changes, then the function updates. I will ask Joe McDaid at Microsoft and see what he says.

@excelisfun 6 лет назад

Glad the video is great for you, MF Wong!!!! Thank you very much for your support!!!!!

@wmfexcel 6 лет назад

@@excelisfunThanks for checking. Looking forward to it. Also curious about the performance of it when working with large dataset... when the dataset is huge, a Pivot Table still take a few seconds to refresh... Can Dynamic Arrays beat Pivot Table?

@excelisfun 6 лет назад

@@wmfexcel , I asked Joe McDaid and he said that the only new Array Function that is volatile is RANDARRAY. Also, Charles Williams, THE most experiences Excel MVP in the World in timing formulas and gauging performance, says that the New Calc Engine and these new Dynamic Arrays are much faster at calculating. I have tried on a million rows (next video I have an example) and they recalculate quickly. Note: for me, when I get anything over 100,000 and I can use the PivotTable Calculations or DAX Calculations (and I don't need the Excel Worksheet for specific features or functions), I am putting my data into the Power Pivot Columnar Database (Data Model). However, there is no doubt that some of the calculations that I make, I want the worksheet, and in this case the new Calc Engine and Dynamic Spilled Arrays will REALLY Come To The Rescue.

@avilammon 6 лет назад

This is awesome.. thank you sir..

@excelisfun 6 лет назад

I agree - it is awesome! You are welcome for the video. Thank you for your support, Sanju : )

@o_oliu7464 5 лет назад

thanks for your video Mike. i like them. but too hard for me, my english is sooo bad. and i need vpn to watch them. hope you happy everyday.

@DIGITAL_COOKING 4 года назад

it is totally amazing!!!! , just one question in the way to have a good knowledge and understand of array formulas just follow this series or should get prior knowledge I don't know if I'm clear in my question mike and thanks

@excelisfun 4 года назад

Thanks, DIGITAL COOKING : )

@Barhomopolis 6 лет назад

At 5:24, the first syntax rule (curly brackets) should be omitted as well.

@excelisfun 6 лет назад

For Array Constants, you still need them.

@Barhomopolis 6 лет назад

Oh! I stand corrected.

@cmiguelmartins 2 года назад

Great tutorial, as usual! Still, I am struglling to do something that I guess should be easy, but I can't figure how to do it. Basically, going to #31, I tried to do it in tabular format instead of Pivot. So, in 2 columns, I get the unique for SalesRep and Product (that is easy), and then with sumifs, I get the total to each combination. That is easy also, if it is not a spill formula. But if I try to create a spilling formula, with #, it does not work. Is there a way to do it?

@cmiguelmartins 2 года назад

In case anyone is facing the same difficulty, I found the answer in "SORTBY, UNIQUE, INDEX and SUMIFS Functions: Dynamic Spilled Array Reports. Excel Magic Trick 1671" ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Vj6yIIvK7p4.html

@erictsang789 4 года назад

Can i select the fields to be filters . for example, a1, a3, a4 has the field that i need. Instead of =filter (A1-a4, ) ? Thanks

@janwillemvanholst 5 лет назад

Mike, I don’t get the excitement. All these examples you are giving are easy to achieve in PQ/PP? or I’m I missing something?

@excelisfun 5 лет назад

Yes, you are. Each tool: Excel Formulas, PivotTables, Power Query, DAX and Data Model have there place. The aspect of formulas that distinguished it from all the others is that it is the only solution that updates instantly when a source input changes. For some solutions this is mandatory. For these types of solutions the dynamic arrays dramatically improve this ability in that the formulas still instantly update but they can also spill to the correct location. Even if you are not using the fancy new Array Functions, the common old stand-bys like FREQUENCY and TRANSPOSE now work without pre-highlighting a range - and this is a significant improvement in terms of getting beginners to use these features. What makes Excel simply the most versatile tool is that we have a lot of different tools and features to accomplish our goals. Then it is just up to us to learn and apply as needed. In all the classes and over 3000 videos I have posted over the last decade, especially the beginner ones, I point out the pros and cons of each tool in hopes that people can then choose : )

@janwillemvanholst 5 лет назад

@@excelisfun Thank!

@excelisfun 5 лет назад

@@janwillemvanholst You are welcome! We are lucky to have such a cool and versatile tool as Excel : ) Thank you for your support on each video with Thumbs Ups and comments : )

@beenay18 4 года назад

@Excellsfun how can I make a accounting software? Is excel enough? should I also require access and vba? or it would be easier with c#?

@CopOr_rob 5 лет назад

I'm trying to find a very latest date in the set of big data with a couple of other conditions using old array formula, what could be the best command under this new Dynamic Array fomula?

@excelisfun3903 5 лет назад

These formulas that return a single date are the same in any version: The latest date is: MAX(Column). The last date in the column (last one at bottom of column) is: =LOOKUP(9.99E+307,Column) I hope you liked the video and will support : )

@721MrG 6 лет назад

Is it new calculation engine be limited to Office 365 only or is it going to be available in Office 2016? Any info on that?

@excelisfun 6 лет назад

MS says it is only available in Office 365.

@721MrG 6 лет назад

@@excelisfun Thank You Sir for quick response.

@KhurrumIqbal1 3 года назад

I have POS reconcilation problem, how do I contact with you

@SyedUsman 3 года назад

You can discuss with me

@mahernasri1885 5 лет назад

Hello Mike, I have bought office 365 and I am following you in this tube, I found out nothing is working as if I have office 2016. looks all new functions are disabled. Can you help me in this problem please ? thanks

@excelisfun 5 лет назад

Microsoft has only made the Dynamic Arrays available in Office 365 Insider. MS says they will not add this feature to Excel 2016 or 2019.

@mahernasri1885 5 лет назад

@@excelisfun how can I get office 365 insider ? is it an upgrade version of office 365? if so, can you send me the link please?

@excelisfun 5 лет назад

@@mahernasri1885 insider.office.com/en-us/

@mahernasri1885 5 лет назад

@@excelisfun It works, thank you.

@excelisfun 5 лет назад

@@mahernasri1885 Yes!!!! : )

@tomazpodlinsek5917 6 лет назад

This functions come also with Office 2019.

@excelisfun 6 лет назад

They do? Thank is REALLY Great News!!!!!

@joachimvangink7472 5 лет назад

To use all these new features you must be in insider program. That mens, that personal data from computer and activities will be send to Microsoft - Who in the world would that do?....

@excelisfun 4 года назад

Good question! They make the tool so amazing, that we have to join, and then they get data.

@kenham5324 4 года назад

So basically I don’t even have to worry about learning how to use the old way of using Array Formulas?

@excelisfun 4 года назад

Well, the fundamentals of array formula, like making direct array operations, logical tests and the like are the same in both. There are just many complicated formulas that we used to have to create that can be done much more easily with the new Dynamic Array Functions, like UNIQUE, SEQUENCE and others.

@OzduSoleilDATA 6 лет назад

This stuff is intense!!!

@ExcelBonanza 6 лет назад

I think this deserves a party! Awesome Video as usual Mike!

@excelisfun 6 лет назад

I agree - it does deserve a party!!!! Let's Paty!! Thank you for your support, Excel Bonanza : )

@ExcelBonanza 6 лет назад

@@excelisfun My pleasure. I have been a long time fan of yours :)

@excelisfun 6 лет назад

Thanks for the long time Fandom : ) Many more years of Excel fun to come, especially with these Arrays : )

@OzduSoleilDATA 6 лет назад

Party at Excel Bonanza's place! I'll bring the sriracha!

@ExcelBonanza 6 лет назад

@@OzduSoleilDATA Yeah, we'll call it the dynamic array Shindig! Seriously, If you come to Toronto, I would love to meet you.

@arunark274 6 лет назад

Your all videos are cool and excited. Love them all 👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍

@excelisfun 6 лет назад

Thanskf ro so many Thumbs Ups!!!!! Glad the videos are exciting for you. Thank you for your support, ARUN, with your comment, Thumbs Up and Subscription : )

@vikasdsem9914 6 лет назад

Only you can define all thing very clearly . Thank for the Video🙌.

@excelisfun 6 лет назад

You are welcome! There is a lot in this video - because there are so many new wonderful things with these Dynamic Arrays in Office 365 Excel!!! Thank you for your support, Vikas : )

@othnielglover-tay6799 6 лет назад

Mike this is AWESOME!! All we need to worry about is "ENTER" as opposed to CTRL+SHIFT+ENTER. Thank you but I'm still keeping your book :)

@excelisfun 6 лет назад

There are lots of great and import things in the book, but where ever it says to use Ctrl + Shift + Enter, just hit Enter : ) : ) Thank you for your support, othniel!!!!

@SyedMuzammilMahasanShahi 6 лет назад

Amazing Mike with another EXCELlent video. Superb ... Thanks Mike.

@excelisfun 6 лет назад

You are welcome, Syed!!!! Thanks for the EXCELlent support : ) : )

@raywang314 6 лет назад

couldn't agree more, epic!

@excelisfun 6 лет назад

Glad it was epic for you, Ray! Thanks for the support : )

@petermyran4986 6 лет назад

Holy Cow - how awesome. I am in the middle of the video now but had to stop to comment. Sorry about the CSE book title ;-} Mike, how do I upgrade my Office 365 subscription to the insider edtion. I think I remember you doing a video on this years ago. But how to now?

@excelisfun 6 лет назад

You have to go to Excel File, then to Account, then click the button. If that does not work, phone Microsoft and ask to convert. Glad you like the videos and thanks for your Holy Cow Support : )

@petermyran4986 6 лет назад

Yeah I don't see a button or any way to change this in my Account. Perhaps it is because I have Office 365 ProPlus? I'll call MS support to ask. I assume it is generally worth being an insider, yes? I am always so impressed and grateful you give us users (fan club) such personal time and attention. THANKS Mike !!!!!!!!

@excelisfun 6 лет назад

I love it, but it means that we are always using a Beta version - so bugs do occur - for example last month, the From Table button in Get & Transform stopped working. They fixed it within a few weeks, though.

@excelisfun 6 лет назад

Personal time and attention: Go Team!!!!!!!

@JonathanExcels 6 лет назад

Multiple criteria vlookup?

@excelisfun 6 лет назад

Yes, as you saw in the video, FILTER can do that!!!!! Thanks for the support, Jonathan!!!

@JonathanExcels 6 лет назад

ExcelIsFun that’s amazing. Noteworthy on its own.

@excelisfun 6 лет назад

It is amazing!!!!! Yes, as I said in the video, I think it is my favorite new use of the new Arrays : ) Noteworthy.

@GeertDelmulle 6 лет назад

WOW! This is a major evolutionary step in the development of Excel! All that complex logic you taught us has now been simplified to the max. From now on it seems that the technical design of these numerical models is going to be as easy as the ‘functional’ design, with almost no added complexity of its own. Amazing! And this will allow for much more advanced numerical models taking a lot less effort. WOW! Oh, and BTW: thank you for this comprehensive video about it: as always: we feel privileged to be in our front row seats, watching this great Excel News!

@excelisfun 6 лет назад

No, no, no... Many things will be simplified, but there are many complex models and even soon to be discovered combinations of these new Array Function that will bring the beauty of the complex formula back, but, many, many formulas have been made more simple. And that is awesome! You are welcome for the Excel News and Excel Array Fun, and as always, thanks fro your support : )

@MalinaC 6 лет назад

It's absolutely awesome! I can't wait my Excel update to try this :)

@excelisfun 6 лет назад

Me too, Teammate Malina : ) I had to wait a few weeks too... since I was not in the first wave of Excel release.

@excelisfun 6 лет назад

Once you get it, though... you will love it - it changes everything, for the better!!!

@MalinaC 6 лет назад

Oh, I love it now, even though I don't have it ;)

@NoShadowOfDoubt1 6 лет назад

I don’t think even Einstein could have wrote that formula in the beginning of the video ...

@excelisfun 6 лет назад

Not Einstein, it was Bill Szysz!!!!!

@excelisfun 6 лет назад

Thanks for your support, NoShadowOfDoubt!!!

@didasssendagi9023 3 года назад

ExcellsFun, Thank you for this course, I have gone through and liked. It will save me amount save me some time wasted on building complex array formulas. But I have a question, If I install office 365 onto my PC, will my colleagues at work who don't use office 365 be able to read my reports? Thanks

@excelisfun 3 года назад

They might be able to read them, but they can edit and change anything in them.

@kylebezner2575 4 года назад

Excel actually is fun!

@qbamc 6 лет назад

I am shocked. Wao! Awesome.

@excelisfun 6 лет назад

I am shocked too - by how much easier it all is now : ) Thank you for your support, Wilfred : )

@sarahmarie8571 5 лет назад

Thank you so much Mr.Mike for this amazing video. Unfortunately, I don't have =unique function in my excel :( :( :(

@spencerclarke4017 Год назад

My VBA textbook brought me here. Awesome, awesome video.

@excelisfun Год назад

Glad you are enjoying. What VBA textbook?

@chrism9037 6 лет назад

Arrays are EXCEL-lent ! Great video Mike

@excelisfun 6 лет назад

I love that: EXCEL-lent!!!! Thanks for your support, Chris : )

@pravinshingadia7337 4 года назад

Mike: I might be being slow first thing in the morning but in the very first example why does the average of Gigi and Amazon calculate to 6027 but if you calculate using AverageIfs then it shows total of 5945?

@raultorvisco2406 6 лет назад

Hi Mike, great video, nice new features coming in. Couple of questions by the way 1)What about these formulas if you send the workbook to someone with previous version of Excel? 2)That Filter function something to do with DAX filter function in PowerPivot/ PowerBi? Thanks a lot!

@excelisfun 6 лет назад

1) If you send a workbook with a new Array Function, like UNIQUE, you will see the values, but you can't edit it; 2) if you enter an array formula with Enter, if you send it to an earlier workbook, the formula will work and it will have the curly brackets. 2) FILTER has nothing at all to do with DAX. DAX Functions are functions that work in the Data Model in Power Pivot and Power BI Desktop, and FILTER is just an Excel Worksheet Function.

@excelisfun 6 лет назад

Thanks for your support with your comments, Thumbs Up and Sub : )

5 лет назад

I have a 260k plus english language words, it is a list. When I apply an array formula to display all entries with a specific set of initial characters the system becomes completely jammed. I had to move my project to another platform. Why does Excel become so ralentized when using this kind of procedures?

@makubexho 2 года назад

Just amazing and awesomely cool

@excelisfun 2 года назад

Glad you like it!!!

@kamranb1369 6 лет назад

Very detailed Mike. Thanks for this :)

@excelisfun 6 лет назад

You are welcome, K B!!!! Thank you for your support : )

@kebincui 3 года назад

G.O.A.T for array formula👍🏻

@excelisfun 3 года назад

Array Formulas are so much fun : ) : ) Glad you enjoy the videos, Kebin!

@wayneedmondson1065 6 лет назад

Hi Mike.. just finally watched this all the way through.. wow.. I need oxygen.. haha!! These new array functions are so cool and I can't wait to see all the ways that you and the other MVPs put them to use. Keep the vids coming.. they are great. Thumbs up!

@excelisfun 6 лет назад

You are welcome, Wayne!!!! Thanks you very much for your support on each video with those Thumbs Ups and comments : ) And, yes, there will be so many more videos because what we can do know in Excel has been so expanded : )

@wayneedmondson1065 5 лет назад

Hi Mike.. just watched this video again.. still amazing! Question: Do you have plans to update your book CTRL+SHIFT+ENTER to include the new dynamic array functions and the new calculation engine? If so, will I have to purchase it again or will you have some sort of update or upgrade cost to existing owners? Just curious. Either way, if you revise the book, I'll upgrade or buy it again. It opened the door to expanding my knowledge of EXCEL. So, worth it.. one way or the other. Thanks again and Thumbs up!

@excelisfun 5 лет назад

@@wayneedmondson1065 I would like to completely re-write the book, but it will be at least a year out, as I am one year backed up on projects... I can't wait, but I just have to find the time.

@wayneedmondson1065 5 лет назад

Hi Mike.. totally understood and glad to hear it will be rewritten in the future. I'll be first in line to order when you release it. In the mean time, there is still a ton for me to learn from the existing version. I'll keep at it. Thanks again. Thumbs up!

@excelisfun 5 лет назад

@@wayneedmondson1065 Thanks for your support in buying the book - and yes, in that book that are so many advanced Excel Formula tricks that we can use in any formula that we create.

@DickyMint10 2 года назад

Love this, but I am trying to use the formula to generate a Data Validation list based upon 2 variables. My formula is as follows: =FILTER('Data'!K4:K57,(('Data'!L4:L57='Industry'!$A$2)*('Data'!J4:J57='Industry'!B5))) where A2 contains the highest level value & B5 contains another value (that holds a unique list of values by Industry). When i paste the calcu in the DV i get an error Msg saying that "The Source currently evaluates to an error". I have proved the formula works if i enter it into a cell but DV does not like it, any thoughts?

@srisharadasamskruthikasangha 3 года назад

I need new excel formula i have a rate in cell as $29.35 -$29.58 and need to subtract $0.34 and i need result in different cell as $ 29.53 to $25.86. Is this possible. hope got my issue.

@Hermiel 4 года назад

Listening to how excited you are about the new Excel functions is downright hilarious. Google sheets has been able to do most of this since its inception. I ditched Excel for Sheets probably a decade ago, and when I have to open it once in a blue moon these days it feels like I'm running in ski boots while carrying shopping bags. To be sure, Sheets has its shortcomings too, but these are far outweighed by its benefits for 90% of use cases.

@yossiperets4172 6 лет назад

Thank you Mike, how about the performance of the new array formulas compared to the old?

@excelisfun 6 лет назад

I have not timed anything yet (I will do some timing some time soon, though), but I really don't have to time on the Sorting Unique List Array Formulas, because I can count all the Array Operations that Excel has to execute in order to get a result. On some of those large formulas there are 20 individual Array Calculations that have to work on the entire range, and with the new SORT and UNIQUE Array Functions, maybe we have 3 array operations. On another note, if you have been watching the MSPTDA class I have been posting, we are starting to study Power Pivot and the Columnar Database, and the amazing thing about that is that when the database is created (when you import data), all data is stored as a unique list, so Unique Count calculations in Power Pivot or Power BI Desktop are almost instantaneous, even of 50 million rows of data!!!!

@hasibahsan15 Год назад

Good morning. I have a question. How do I count different words from a single column in excel 365.

@HusseinKorish 6 лет назад

Perfect video

@excelisfun 6 лет назад

Glad the video was perfect for you!!!! Thank you for the support!

@pravinshingadia7337 4 года назад

Thanks for another great video! Thanks for letting us know about the Insider Program - joined last week and worked through the examples. You are a star!

@excelisfun 4 года назад

Glad that i can help. Also glad that you got the Insider. Even though it will be a few years before most Excel users are on Office 365, this is the future and it is awesome!

@LearnAccessByCrystal 4 года назад

looks great, Mike @ExcelisFun! I've marked this video to share the link (and the other one I just saw too) ... and for myself to come back to when I have more time to watch. Great index in your video description, covering lots of different functions. thank you

@christopherneu726 5 лет назад

Is it possible to skip columns in FILTER?

@pinaki1162 5 лет назад

Sorry , I think it was a problem with my internet.

@pinaki1162 5 лет назад

Sorry I am unable to come to the same answer in the Average calculation. The answer which is generated by the formula (=AVERAGE(IF((A28:A34=E28)+(B28:B34=F28),C28:C34)) given in the Column is wrong. The average of only Amazon Values is 6053 ((5610+6270+6279)/3) and not 6027. Similarly for Gigi alone it is ((2546+5610+9428+6279)/4)=5967. What does the value actually being shown? Is it for Gigi or Amazon? Please explain

@maxy2032 5 лет назад

(6,279+9,428+6,270+5,610+2,546) / 5 = 6,027 I don't who would take an average like that in the real world. Average(if(Mixing apples & oranges))

@txreal2 5 лет назад

At about 8:20, simple question that's bugging me, appreciate any comment. The + sign used in between IF conditions (Gigi+Amazon - cells that are both Red+Yellow) indicates to me that it's an AND function; not OR function. But it's an AND. If it's OR function, then the Average = (6,279 + 5,610)/2 = 5,945 Is this just Exel formatting peculiarity?

@excelisfun 5 лет назад

+ is OR Logical Test * is and AND Logical Test. Period. Excel Array Formulas, Computer Languages, Statistics, Logic...

@rob4canada 4 года назад

There here for us non-insider edition users now! Though it looks like "SINGLE" has been changed to "@". So at 35:15 in the video where he explains SINGLE, it should not be =@$H$7:$H$10 instead of =SINGLE($H$7:$H$10). Probably not important for 99.9% of the users.

@excelisfun 4 года назад

Thank you, good point, Robert : )

@Barhomopolis 6 лет назад

Can't wait for Excel to give me all those #SPILL! errors!

@excelisfun 6 лет назад

Yes!!!!!

@Victor-ol1lo 6 лет назад

Awesome video Mike !! In the beginning I thought we are simply talking about new functions in Excel but you showed the real power behind the new engine.... This is a real game changer !! Thums Up and thanks !!

@excelisfun 6 лет назад

Victor... It is way more amazing than I tried to show in this video... It does change everything: for easier formulas and then more amazing formulas than we ever thought possible. Over the days, and weeks and months and years, we will see the TRUE transformational power of this new Excel Engine : )

@excelisfun 6 лет назад

Thank you, Victor, for your support of this excelisfun channel : )

@joelwootten9158 5 лет назад

Mike, can you populate the value by its quantity? For example if "product x" has 36 subproducts and I select "product x", I want to see literally "product x" in a column 36 times in a list. I am an amatuer in the fact that I manage to write excessive complicated formulas too big for excel to manage simply because I can't learn them fast enough for valuable application. Really appreciate any feedback.

@excelisfun 5 лет назад

Wow, Joel, I have never been asked that before and therefore have never tried to do it. I do not understand exactly what you are asking because some of the words you use do not make sense to me. But if you want to repeat a word like "Quad" 12 times across the columns, this formula will work in Office 365 Insider Edition: =IF(SEQUENCE(1,12)

@nimrodzik1 6 лет назад

Stuff is awesome. I can't wait my Excel update

@excelisfun 6 лет назад

Yes, it will change the way we use Excel : ) Thank you for the support, nimrodzik1, with your comment, Thumbs Up and Sub : )

@ExceliAdam 6 лет назад

I love Modern Dynamic Array Formula. What you did to get them, because not all Office Insiders have them.

@excelisfun 6 лет назад

I do not know the mysteries of how Microsoft runs the Insider Program. Even though I wrote the only book about Array Formulas and worked with Microsoft as they created the New Dynamic Arrays, I was not one of the first to get this. Others were making videos about these amazing features before me because they had the Dynamic Arrays, but my computer did not. My Office Insider just got these last week... So I am the wrong person to ask about how to get these, because I am like you: I am still left wondering how I got them and how others can get them. The word from Microsoft is only: Soon...

@romerog100 5 лет назад

Hi Mike, why all these array formulas disappeared in Excel 365? where can I find them now?

@excelisfun 4 года назад

They are only in the Insider Office 365.

@AweshBhornya-ExcelforNewbies 5 лет назад

Awesome

@jerrydellasala7643 4 года назад

Mike, the Average formula at 8:28 (section 4) evaluates to 6,027. I had a comma after the "C28:C34" in the formula which was giving an answer very different from what was in the Video, so in trying to figure out where the problem was I used the following formula "=AVERAGE(C28,C28,C29,C31,C32,C32,C33)", but that evaluated to 6,003 instead of the 6,027 in the video. I then created a table with Rep/Cust in one column and Value in the other. In that table the value 6,279 is listed twice - once for Gigi, once for Amazon, and with that table the average was 6,003. I then created another column where a value was shown only once for each row, so 6,279 showed up in the column only once, and that average was 6,027. To put it in DA Terms, the Array formula in the video is evaluating the average at the Line or "Invoice" level, but the manual formula is evaluating the average at the Item level. To get the value 6,003 or the item level average, the formula "=AVERAGE(IF(A28:B34=E28,C28:C34),IF(A28:B34=F28,C28:C34))" worked! Does that make sense?

@planxlsm 4 года назад

11. (13:45) OR Logical Test Adding formula with SUMIFS & SUM Function, rather than SUMPRODUCT 25. (29:43) Spill Direct for Dynamic Arrays using Array Syntax 29. (32:28) MID, LEN and SEQUENCE to extract characters from a cell in a Row 31. (33:40) Dynamic PivotTable using SUMIFS and a number of new Array Functions

@נועהאורן 4 года назад

The new Excel Calculation Engine is real magic. It was worth waiting for it. Thanks so much for the detailed explanation. What happens if I share files with the dynamic array formulas with people who don't have Office 365?

@nadermounir8228 4 года назад

great Video Mike :) one point puzzling me. when sorting a list with the sort function spilling a result of multiple rows and columns. Since the formula lives in the top left cell, we are then unable to make any changes to the spilled columns. in other words, if a value of any of the cells in the second column is zero, i want it to show "none" so in the old dynamic array, i would wrap the whole function with an IF function. however, in the spilled arrays i am very limited an unable to do this. any thoughts?

@sarawu9150 5 лет назад

I am using Excel 2013, I tried and why I still have to use ctrl+shift+enter for array formula???? something wrong with me?

@MohamedAlyCLAY 4 года назад

now we have new excel we have to learn :)

@excelisfun 4 года назад

Yes!!!! They are so amazing and make what we did in the past so much easier. Here is the full playlist: ru-vid.com/group/PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx

@riteshpandey5082 4 года назад

Sir, from where I can get your book ctrl+shift+enter.

@excelisfun 4 года назад

www.amazon.com/Shift-Enter-Mastering-Excel-Formulas/dp/1615470077

@sanukumar7122 4 года назад

thanks

@SciJoy 5 лет назад

My mind is blown. So excited.

@wayneedmondson1065 5 лет назад

Hey Mike.. now that I have Insider on my laptop, I re-viewed this video and walked through the 31 exercises. Truly amazing new functionality and so much fun too! I'm going to re-watch your entire series to tune up my DAF skills. Thanks again and Thumbs up!

@vosk875 Год назад

Excellent tutorial as usual. Can you please tell me why the following formula results in an error: =sumifs(VSTACK(fSales[Sales ($)],E100:E103),VSTACK(fSales[Product],C100:C103),G6#) Thank you!

@excelisfun Год назад

SUMIFS, COUNTIFS and the link can not handle array operations in the sum_range or crieria_range. Microsoft programmed it this way many years ago. It has been causing probalmes ever since.

@vosk875 Год назад

@@excelisfun Right, it's easy to forget as VSTACK is essentially an array operations. Funny thing is this works (referring back to spilled VSTACK ranges: =SUMIFS(I91#,H91#,G91#). Sometimes when learning more advanced array operations, you forget that XY# is not an array operator. Thanks for the kind reminder.

@MalinaC 4 года назад

It is deffinately AMAZING what Excel can do! It always was, but now it is so easy :). Thanks for this EXCELlent lesson! Thumbs up of course!

@excelisfun 4 года назад

I agree about how Excel is so infinitely amazing : ) Thanks for your support, teammate Malina : )

@VSP4591 4 года назад

This is fantastic. More easy but all our knowledge is lost. Mike, this is a real progress. We have to keep learning from you.

@faizs9676 5 лет назад

Help: I have correlation to be measured on 3 hrs 10 hrs 15 hrs, 24 hrs, etc... I am not an expert in excel..... I can handle the formulas in cells... I want to avoid interface if possible I was fixing a cell ("fix cell") where i will insert 3,10,15,24...etc and this will update the correlation formula range 3 to 5 to 10 to 15.... as and when the change is made in "fix cell". I am unable to figure out even after googling and on youtube. I think this has got to do some thing with row() and column().... The time on Row and impulse category is headers for column. e1 e2 e3 s1 s2 s3 etc

@excelisfun 5 лет назад

I am not sure how to do that. It sounds like you need some back and forth dialog to help get a solution. The best Excel Question site I know is: mrexcel.com/forum. But you have to ask questions people can understand and reply to their queries with detailed replies.

@faizs9676 5 лет назад

@@excelisfun Many Thanks for you kind and quick reply. Yes I will check that