Fn+F5-ing My Excel Skills: Dynamic Arrays
I’ve recently completed the LinkedIn course on Excel’s Dynamic Array Functions (DAFs) by Oz du Soleil.
As a Maverick Excel user who first played with it back in 2002 at the age of 16
It’s been a big part of my life ever since, to do simple calculation like I did right there, all the way to designing economic models with fully fledged probability and seasonality predictions, to visualizing data for status reports and board meeting updates.
Man do I wish I had DAFs back in the day when Index, Match, V-Lookup, H-Lookup, and a small number of functions were all you had when doing major projects with no budget even for access to better tools.
Microsoft Excel 2019 and Automation Capabilities
As my second pursuit for 2021 is to polish my skills in data analytics, I wanted to not only revisit the old stuff, but also check out some of the new cool features in Excel 2019.
The new features are great for those of us who had to write page long excel functions and used enough brackets to rebuild the Hoover Dam.
Here’s a sample of what I’m currently looking at and using in my refresh exercises:
MAXIFS and MINIFS: saves a lot of time. previously, I would’ve used a complex grouping of “if functions” to make this function from scratch.
TEXTJOIN: DEAR GOD! the concatenate function, but better! previously the function would look something like this:
This is just the beginning, the updates most exciting are also in
Pivot Tables are becoming more intuitive with multi select slicers and more, bringing Excel users no-code analytical capabilities found on bigger BI tools. As much as I like the automation pivot tables bring, I still enjoy using regular Excel functions to get similar and more customizable results.
And when dragging down a cell or function improperly could’ve cost you your reputation or career 😭😭
Back to Dynamic Arrays: Solving Challenge #1
MISSION
1- List the movies, by Released date. The oldest movie is first.
2- Omit all comedies
3- List the dates we’re going to see each movie: every 4 days starting on 29AUG19
THE DATASET
The dataset for this exercise is small and clean so thankfully no data clean-up necessary prior to solving.
First Step
understand the data and breakdown the process of the mission:
I first turned the data into a table to ease the extraction and function writing process. I named my table “ratings”, a name I can remember as a description of this table and a name that isn’t in conflict with any of the columns.
I then moved to focus on the key columns or rows needed for this challenge.
I thought of what DAFs I can use to reach the conclusions:
Since there’s sorting required, I decided to go with a “=SORTBY()” function to sort my movies by release date, and then, since movies are my 1st column, follow that with a simple “=VLOOKUP()” function.
I selected movies and genres as the columns I want to sort, then selected Released as my sorting criteria.
since ascending is the natural order, I didn’t need to specify it in my function, otherwise you can follow the sorting criteria by selecting: (1) ascending, or (-1) descending.
Result of Step #1:
PROTIP: notice that the “101” movie title is on the right side of the cell, that’s because even though the Data Type was originally text. When we do DAFs the default Data Type is general, if you click on the Year too, you’d notice it’s not a date anymore. Keep it like that, if you change it to text, you won’t be able to edit the formulas (because the formula would be recognized as text input).
Steps #2 and 3
2- Omit all comedies
Using the new table as my new source table, I decided to use a FILTER function to omit the comedies, and I used the filter denoting exclusion “<>” to get the table below:
3- List the dates we’re going to see each movie: every 4 days starting on 29AUG19:
Now comes the “fun” part. knowing this is a date, and I needed to add a SEQUENCE of every 4 days, there’s really two ways to do this, here’s my trick:
The most annoying thing for a lot of Excel users is how excel reads dates, but fear not, we will use this witchery against itself with this reverse spell:
- Enter 29 Aug. 2019 into a cell
- Now change it from date to general
- You’ll get “43706”
You can even confirm this by doing the “ =‘X’=’Y’ ” function which tells you if something is equal, so “True”, or something is not “False”.
BONUS! Wanna be really nerdy but do something useless?
=type() function to tell you which one is a logical value (denoted as 4) and which one is a number (denoted as 1). Comes in real handy in other cases
First method: SEQUENCE
To create the sequence, I’d need to know the number of days based on the number of movies, and this is where COUNTA really comes in handy!
- I counted the days
- Told it I just want a sequence for one column (‘1’)
- Gave it the first day I’m basing the sequence on (43706) originally 29 aug 2019
- And then told it I want a sequence of 4 days and voila!
My old method: (“date as a number”+4)
Dates are a sequence in excel! so when we get the date 43706, you’ll get the next day simply by adding 1, but in this case, since the sequence is 4, we are adding 4 to our original number and BOOM!
Audits:
auditing your data in excel is extremely easy and important, in my audit here, I am using 2 things:
- Comparing and getting a logical value
- Using conditional formatting to help us visualize errors, comes in handy in larger datasets, and especially when the SEQUENCE is manual
In the below example I changed one of the values to 0, which clearly messed up my sequence! so now not only can I see the FALSE flag, I can even see where the sequence was off!
Conclusion
Not only are my auditing tricks not necessary when using these functions, I really can sit back, relax, and let the automation “help” me reach a conclusion or do my analytics.
You won’t be replaced by machines, but you gotta learn how to work with them!
🤖💓
Feel free to check out the workbook HERE.
