What are Dynamic Array formulas and why you should use them?
In this blog article (and few other upcoming blogs), I am going to write about the capabilities of Dynamic Array (DA) functions with examples and demonstrate to you some great features it has that I believe can empower the PA Analysts to do all differently sorts of data analysis in a simpler and much more intuitive way, thereby enhancing their productivity.
To start off, lets first understand what Dynamic Array functions actually are?
To put it simply, the DA functions are those functions that leverages Excel’s latest DA calculation behavior where you no more have to enter CSE(Control+Shift+Enter) to spill the formulas or in fact copy pasting the formula for each value you wanted returned to the grid.
With DA you simply enter the formula in one cell and hit enter and it will result in an array of values returned to the gird, also known as spilling.
The Array functions are currently only supported in Office 365 but according to Microsoft, it will be extended other versions soon.
Typically when you enter a formula that may return an Array in Older version of Excel and then open it in DA version of Excel, you would get an @ sign – also know as implicit intersection - before the formula. This is added by Excel automatically for all the formulas that it considers might potentially return an multi-cell ranges. By having this sign, Excel ensures formulas that can return multiple values in DA compatible version would always return just one value and it does not spill.
Following is the information on implicit intersection available on the Microsoft website:
With the advent of dynamic arrays, Excel is no longer limited to returning single values from formulas, so invisible implicit intersection is no longer needed. Where an old Excel formula could invisibly trigger implicit intersection, dynamic array enabled Excel shows where it would have occurred. With the initial release of dynamic arrays, Excel indicated where this occurred by using the SINGLE function. However, based on user feedback, we’ve moved to a more succinct notation: the @ operator.
Note: According to Microsoft, this shouldn’t impact the current formulas, however few Planning Analytics clients have already complained of issues having @ in DBRW formulas in PAfE where it no more works. The @ sign that had to be manually removed from all DBRW formulas to make it work. This is a bit of bummer because depending on the number of reports, it may lead to significant amount of work to do this task, a VBA might be a of relief here otherwise it a bit of tedious task.
More on Implicit Intersection can be found in below link:
Additionally, there is another key update that must be made in Excel setting to address another bizarre side effect of implicit intersection observed in Dynamic reports. See below link for details:
https://www.ibm.com/support/pages/unable-expandcollapse-tm1rptrow-dynamic-report-shows-mail-icon
Below are the list of DA formulas currently available in Excel 365.
• FILTER
• RANDARRAY
• SEQUENCE
• SORT
• SORTBY
• UNIQUE
I will be covering off a bit more in detail on these functions in my subsequent blogs to showcase real power of these functions so hang in there till then.
As for why you need to use them, below are some of the reasons I’ve listed to bring on the table:
1. It compliments the PAfE capabilities and fills the gaps where PAfE could not due to its limitations
2. Can open up the world of new data analysis capabilities
3. Once you understand the formulas and the Boolean concept (which is not complicated by any means), It’s true potential could be realised
4. It is simple yet very powerful and a big time-saver
5. With formula sitting only in one cell, it is less error prone
6. The calculation performance is super-fast
7. CSE no more!
8. It is backward compatible, meaning you need not worry how your DA results appear in legacy excel as long as you’re not using the DA fun
9. Updates are easy to make – need to only update in once cell as opposed to all cells
This is my value proposition for why you should use DA formulas. I’ve so far not yet demonstrated what I proposed which I intend to do in my later blogs, till then thanks for reading folks and stay safe.