I covered a solution to sorting and ignoring the sign a couple of years back, but it is time to revisit this thanks to dynamic arrays.
It is now easier to create a distinct count formula in the subscription version of Excel. You can also use a criteria. A distinct count only counts each value once. Duplicate entries are ignored.
A spill range is the result of a dynamic array formula. At the moment that requires the subscription version of Excel.
In budgets, forecasts, financial models and even reporting models repeating the numbers 1 to 12 can be useful. The SEQUENCE and MOD functions can make it easy and scalable.
I wrote a blog post a few years back showing how to add up numbers formatted as text. If you have the subscription version of Excel you have another solution.
Let’s say we need to put a prefix in front of a number to identify the period being used. Whether that be year, month or week.
One of the frustrations with using array syntax is that you always have to type all the entries between the curly brackets. You couldn’t link to cells. Well that has all changed with dynamic arrays.
In this post I finish off the Calendar matrix by adding holidays.
The SEQUENCE function returns sequential numbers. Let’s see how we can use it to create a Calendar matrix.
The SEQUENCE function returns sequential numbers. Let’s see how we can use it with a list of dates.
Many things that were hard or complex are now much simpler. Creating dynamic drop down lists based on previous selections used to be tricky in Excel. Dynamic arrays make it straightforward.
With the introduction of Dynamic Arrays in all versions of Microsoft 365 (formerly Office 365) it is now a lot easier to use the TRANSPOSE function.
With introduction of Dynamic Arrays in Office 365 Excel has one new formula symbol and another that was previously only use in formatted tables.
Dynamic arrays have the potential to change the way Excel spreadsheets are created. They were released in the January 2020 wave of updates to the Office 365 subscription version.