MrExcel.com - Your One Stop for Excel Tips & Solutions

Extend Excel Dynamic Arrays With FastExcel SpeedTools Version 4

February 03, 2020 - by

Extend Excel Dynamic Arrays With FastExcel SpeedTools Version 4

I am a big fan of the new Dynamic Arrays rolling out to Excel. It took the Calc team years to bring these functions to market. Once you start really using Dynamic Arrays, you find out what features would make the functions a bit more useful.

Charles Williams is an Excel MVP in England. He is one guy, sitting in an office some 4,695 miles west of Redmond. In what Charles estimates as two- to three- man weeks, Charles built 44 enhancements to Dynamic Arrays. I’ve downloaded the beta of the software and the results are jaw-dropping. Now that I’ve seen them, I am not sure how I would live without them.

Vote for Microsoft to Add These Functions

My reaction: if the entire Excel Calc team took this long for Dynamic Arrays, how did one guy spending three weeks, manage to do so much?

You can cast your vote to ask the Excel team to add similar functionality here: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/38952709-sumcols-and-sumrows-functions-for-dynamic-arrays

Buy SpeedTools

Charles is an altruistic guy: He has invited the Excel team to borrow anything they like from his list of 44. But he also realizes that with the red tape in Redmond, plus the need to have everything work in all versions of Excel (Excel for your grandmother’s cell phone, Excel for the Commodore-64, Excel Online), it will take closer to 300 days instead of 3 weeks for Microsoft to react. In the meantime, Charles will be selling the functions as part of his SpeedTools V4 add-in for US $69. You might consider the FastExcel V4 Bundle for $219 with an introductory 40% discount through April 15 2020 using code FXLV4INTRO. The bundle adds tools for finding calculation bottlenecks, a formula explorer, name manager, and a sheet manager.

SpeedTools Version 4 Benefit Number 1: Add Totals to a Dynamic Array

Say that you have a formula generating a dynamic array. The number of rows and columns of the dynamic array will vary. Joe McDaid gave us a way to get the total of the entire array with the D6# reference.

Excel offers a way to refer to the entire array with D6#.
Excel offers a way to refer to the entire array with D6#.

But what if you want a total row or a total column? When someone changes the input cells in B3 or B4, the array will resize. How would you know where to put the SUM functions if you don't know how tall the resulting array will be? In the above image, totals belong in Row 9. In the following image, they belong in row 11.

With the size of the array changing, how can you predict where the totals should go?
With the size of the array changing, how can you predict where the totals should go?

Buy the FastExcel SpeedTools V4 add-in and you have access to the new TOTALS function. It returns the original array with a new total row, as shown in row 14 below. The new HTOTALS function adds a total column as shown in column H below.

The SpeedTools V4 add-in enables TOTALS and HTOTALS.
The SpeedTools V4 add-in enables TOTALS and HTOTALS.

Of course, the previous image is showing the long way to go. You don't need three separate formulas, simply wrap HTOTALS(TOTALS( )) around the original formula and you will have a total row and column.

Add a total row and column with two nested functions.
Add a total row and column with two nested functions.

Charles Williams is the world's foremost expert on Formula Speed. The Help document for his add-in points out that TOTALS and HTOTALS are multi-threaded and non volatile. Nice.

Watch Video

Title Photo: Arnaud Mariat