Yes, but we have been using that painful way for decades... But it is now a new and easier frontier!!!! Thanks for your support and comments, Chris !!!!
FREQUENCY was/is cool but now so many of those crazy "work with uniques" formulas are much easier to write and understand for a regular Excel user :) Is it good or bad for us (folks who know Excel a little bit better than the average user)? :)
It is good. It is especially good since most of us will work for many years into the future, and although currently few people have the new functions, in a few years everyone will be on Office 365, and so we will all have the same version and can use these new and easier formulas and share freely : ) Thanks for your comment, Teammate!!!!
I will use dynamic arrays for big data only :-) Mike, because daynamic arrays make us lazy to use the traditional function. Thanks for frequency function refresh to find unique values. Thanks a lottttt
Hi Mike.. way cool use of DAF vs. traditional array formula. I especially like the use of ROWS() vs. COUNT() for the performance advantage on a large data set. Had not thought of that before watching your video. Thanks for another clever and unique tip and technique. Thumbs up!
The traditional CSE formula is rock solid and it will work in any Excel version this wins for me. Let me add that the construct with the new calc engine is not so easy at all.
Watching how easy this is with the new dynamic arrays is painful as I’m using CSE on a daily basis... come on Microsoft - let’s release this stuff already. Great video Mike.
Cool! I haven't used the frequency formula that much, but somehow the logic of Office 365 version seems to be more, hmm, logical? :-) Anyway, thank you for both solutions! Will be trying them out "in the real world" soon.
Thank you Mike, this was a fair comarisson between the old and the new world with formulas. But "in the middle" there was/is an easier solution, not with formulas however: Just make a pivot and add the data tot the datamodel (checkmark), then put product on rows, and dates to values. It will count by default, but you can change this to ...."distinct count"....Although not formulas, it is faster to set up.... Greetings, Bart
No worries, I have playlist of videos to learn most topics in Excel. Here is an Array Formula Playlist that teaches a LOT about Array Formulas: ruclips.net/p/PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci
@@simfinso858 I have tried to post lots of stuff for the last decade : ) But since I have never posted videos about Excel on the Phone, that is where a Teammate like you can help the awesome Online Excel Team!!! : )
ExcelIsFun oh Great but phone Excel have Limitations that Even You use External keyboard ctrl + Shift + Enter doesn't work ( or maybe I don't know how to use it I tried but I didn't get much success.) You Don't have pivot table & Many stuff but if You have Formula ( Guys like You who knows all the Formulas & keyboard shortcuts) You can do a lot of things .Here I am trying to explore as Much as I can do with this phone Excel.
When I try "Before Dynamic Arrays" method, I'm getting "You can't use more that 8192 characters in an Microsoft Excel formula" error when I press F9 on first IF statement. You are not getting that error.
The first one was very nice, but the DAF variant is way more elegant IMO. I’m still counting the days until my excel at work has DAF (no pun intended).
Love the pun!!!! Me too - because I can't wait to start teaching these in my Highline Classroom classes also : ) Thanks for your amazing support, Geert : )
the new functions are neat and all, but i'm not seeing how they are more efficient and more user friendly than pivot tables or perhaps a power query option.
It is simple. Since the PivotTable came out in mid 90s and then Power Query in 2013, you use formulas when you need the solution to update instantly. No other feature does this. For some business solutions this requirement is mandatory. For these situations, these new array formula are simply spectacular. BTW, QuickMadeUpName, I am not sure if you have seen all the class that I teach here at RUclips, but in all these classes I teach the fundamentals of formulas, PivotTables, Power Query, Power Pivot and Power BI in a way that makes these tradeoffs between the different awesome Excel tools we have clear. We are lucky that Microsoft provides all these tools, and each has its place when considering how to build a particular solution. ,
@@excelisfun thanks for the reply Mike. Actually I've probably watched about 600 videos of your videos and have bought and read both of your books!! I'll try to see the benefit of these new functions. Thanks again for the reply.
Brilliant Mike, you have made life easy for many excel users. Thanks a lot
You are welcome a lot! Thanks for the support, Sanjeev!!!
The "Before" way is painful Mike! I can't wait until my Office 365 gets the new Filter function! Great video
Yes, but we have been using that painful way for decades... But it is now a new and easier frontier!!!! Thanks for your support and comments, Chris !!!!
I love learning excel with your method Mike... Thanks again :)
You are welcome, edgie!!!! Thanks for your amazing support : )
Love dynamic arrays. So easy to use! Thank you Mike for another great tutorial.
You are welcome Teammate : ) : )
FREQUENCY was/is cool but now so many of those crazy "work with uniques" formulas are much easier to write and understand for a regular Excel user :)
Is it good or bad for us (folks who know Excel a little bit better than the average user)? :)
It is good. It is especially good since most of us will work for many years into the future, and although currently few people have the new functions, in a few years everyone will be on Office 365, and so we will all have the same version and can use these new and easier formulas and share freely : ) Thanks for your comment, Teammate!!!!
@@excelisfun :)
Great way to show the advantages of using the new DAF. In your competition the winner is clear!!
Yes, there is definitely an advantage to the DAF : ) Thanks for the comment and support, Richard : )
I will use dynamic arrays for big data only :-) Mike, because daynamic arrays make us lazy to use the traditional function. Thanks for frequency function refresh to find unique values. Thanks a lottttt
Thanks for sharing this EXCELlent video Mike. This is amazing
thank you so much Mr. Mike that was great to know I really appreciate
You are welcome, Ismail!!! Thanks for your consistent support!!!
wow life is much easier~ the 1 dislike must really miss the array formulas
Thanks, Excel Bear!!!!!
As always great vidéo .thank you
Hi Mike.. way cool use of DAF vs. traditional array formula. I especially like the use of ROWS() vs. COUNT() for the performance advantage on a large data set. Had not thought of that before watching your video. Thanks for another clever and unique tip and technique. Thumbs up!
I learned that tip (between ROWS and COUNT) in a Duel video with Mr Excel many years ago... : ) Thanks for having fun and watching, Wayne!!!!
The traditional CSE formula is rock solid and it will work in any Excel version this wins for me. Let me add that the construct with the new calc engine is not so easy at all.
Thanks for the feedback, ExcelStategy : )
Great! Thank you for your content and passion!
You are welcome, David!!!! Thanks for the support : )
Great video especially the old school one :)
Great to know both methods
It is nice to know both : ) Thanks for the support, Vida!!!
Watching how easy this is with the new dynamic arrays is painful as I’m using CSE on a daily basis... come on Microsoft - let’s release this stuff already. Great video Mike.
Cool! I haven't used the frequency formula that much, but somehow the logic of Office 365 version seems to be more, hmm, logical? :-) Anyway, thank you for both solutions! Will be trying them out "in the real world" soon.
Yes, the newer functions seem to be much more conceptually easy to understand. Thanks for your comment and support, Sandra!!!!
Great video.
The Count Unique formula would be even better if there was a way to spill it rather than copying it down the rows.
I agree.
Thank you Mike, this was a fair comarisson between the old and the new world with formulas. But "in the middle" there was/is an easier solution, not with formulas however: Just make a pivot and add the data tot the datamodel (checkmark), then put product on rows, and dates to values. It will count by default, but you can change this to ...."distinct count"....Although not formulas, it is faster to set up.... Greetings, Bart
Yes, great way to do it, Bart!!!! : )
Thanks Mike
One more time brilliant :)
You are welcome, Sevag!!!!
After watching this video I feel I am thousands miles away from this knowledge .I have to learn a lot of things .Great video
No worries, I have playlist of videos to learn most topics in Excel. Here is an Array Formula Playlist that teaches a LOT about Array Formulas: ruclips.net/p/PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci
ExcelIsFun You Are an Excel university
@@simfinso858 I have tried to post lots of stuff for the last decade : ) But since I have never posted videos about Excel on the Phone, that is where a Teammate like you can help the awesome Online Excel Team!!! : )
ExcelIsFun oh Great but phone Excel have Limitations that Even You use External keyboard ctrl + Shift + Enter doesn't work ( or maybe I don't know how to use it I tried but I didn't get much success.) You Don't have pivot table & Many stuff but if You have Formula ( Guys like You who knows all the Formulas & keyboard shortcuts) You can do a lot of things .Here I am trying to explore as Much as I can do with this phone Excel.
@@simfinso858 Yes, Phone Excel can't do most of What a Computer Excel can do. But, it can make finance calculations well, as you show : )
Yet to get the new filter function in my office365
Amazing .... Thanks Mike
Glad it is amazing, Hussein!!! Thanks for your support : )
Nice!
Nice comment, Oz : ) : )
When I try "Before Dynamic Arrays" method, I'm getting "You can't use more that 8192 characters in an Microsoft Excel formula" error when I press F9 on first IF statement. You are not getting that error.
Thanks Mike. That was great fun watching. :) :)
Fun to watch, fun to make -- it is a win win : ) Thanks for your support, John!
The first one was very nice, but the DAF variant is way more elegant IMO.
I’m still counting the days until my excel at work has DAF (no pun intended).
Love the pun!!!! Me too - because I can't wait to start teaching these in my Highline Classroom classes also : ) Thanks for your amazing support, Geert : )
You’re welcome. And you deserve every bit of all of our support.
See you on the next one.
@@GeertDelmulle , Can't wait for the next one : )
="A unique Like!"
Thanks : )
the new functions are neat and all, but i'm not seeing how they are more efficient and more user friendly than pivot tables or perhaps a power query option.
It is simple. Since the PivotTable came out in mid 90s and then Power Query in 2013, you use formulas when you need the solution to update instantly. No other feature does this. For some business solutions this requirement is mandatory. For these situations, these new array formula are simply spectacular. BTW,
QuickMadeUpName, I am not sure if you have seen all the class that I teach here at RUclips, but in all these classes I teach the fundamentals of formulas, PivotTables, Power Query, Power Pivot and Power BI in a way that makes these tradeoffs between the different awesome Excel tools we have clear. We are lucky that Microsoft provides all these tools, and each has its place when considering how to build a particular solution.
,
@@excelisfun thanks for the reply Mike. Actually I've probably watched about 600 videos of your videos and have bought and read both of your books!! I'll try to see the benefit of these new functions. Thanks again for the reply.
Now if Microsoft you just release the new calc engine to the general public, I'd actually get to do this.
They say that ONLY Office 365 will have this. But it should be released in the next few months.