Download Excel File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1534-1535.xlsx
Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm
In this video learn two methods using Excel Spreadsheet Formulas to calculate a Unique Count of Dates Product Was Sold. Learn about the New Office 365 Dynamic Array Formulas and New Excel Calculation Engine or the Traditional Excel Array Formula Method. Learn about the new Office 365 FILTER Array Function, UNIQUE Array Function. See the standard Excel Functions: FREQUENCY, IF, SUM, ROWS and COUNT.
Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516) https://www.youtube.com/watch?v=7jJMDGZpjSk
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!
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
+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.
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 YouTube, 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.
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.
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 agree: it is fun to know the old and new : ) But here is the funny thing: we are only a few months into the new arrays, and I already dream up my formulas with the new methods, not the old... Thanks for your support, Kevin : )
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.
+Finance in 5 Minutes 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!!! : )
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.
Sechskies Eun Ji Won and rookie singers Lee Soo Hyun and Kim Eun Bi performed the third OST single titled "Love Song". The rookies, who are both training to debut in HYWY Entertainments girl group HYWY Girls, joined the veteran to sing about falling in love with an unlikely person. The rhythmic medium temp track is the perfect tune to make your spring days even brighter.
As a child, there was a portrait in our family home in Paris that I always loved. Today, it’s known as Maya with Doll – but to me it was just a portrait of my mother, albeit a remarkable one. “Your grandfather was a painter,” she would say, whenever the subject of the canvas, one of many that hung around the house, came up in discussion. It was only when I began school, and whispers about my heritage started to follow me, that I realised what an understatement that was. My grandfather was far more than a painter. He was the defining figure of 20th-century art – and, as I would learn later from years of academic study, a true genius. It was a revelation that would shape the course of my life in many ways. When Picasso died – in 1973, the year before I was born – he left behind 45,000 works, not to mention personal objects and correspondence.