Advanced Formula Magic: Running total by row with dynamic arrays in Excel

Summary

In this tutorial, we dive into Excel's dynamic array functions to solve the challenge of calculating running totals, even when the order of data changes. Starting with a simple scenario of running totals by row, we explore how to handle complex data like regions instead of quarters. By leveraging functions like `UNIQUE`, `TRANSPOSE`, `SUMIFS`, `MATCH`, `FILTER`, and `MAKEARRAY`, we demonstrate how to create a flexible, dynamic solution without relying on static helper columns. The result is an efficient way to calculate running totals by row or column, even with re-ordered data.

Takeaways

• 😀 Use Dynamic Arrays to handle complex running total calculations in Excel with ease.
• 😀 Start by creating unique lists for your items and quarters using the `UNIQUE()` function.
• 😀 Transpose the list of quarters horizontally to fit your desired layout.
• 😀 Initially use the `SUMIFS()` function to calculate running totals by row, assuming alphabetical order for the headers.
• 😀 If the order of headers changes (e.g., from alphabetical to specific regions like North, South, East, West), your previous approach may no longer work.
• 😀 Use the `MATCH()` function to calculate the position of each row and column for a more flexible solution.
• 😀 The `FILTER()` function, combined with `SUM()`, allows you to calculate the running total based on dynamic conditions.
• 😀 Use `MAKEARRAY()` to handle multiple rows and columns dynamically, enabling the calculation to 'spill' across a range.
• 😀 Simplify your formula by using the `LET()` function, which allows for the definition of intermediate variables and reduces reliance on helper columns.
• 😀 The final formula avoids helper columns and dynamically calculates running totals based on both rows and columns.
• 😀 You can easily switch between calculating running totals by row or by column by modifying the comparison operators in the formula.

Q & A

What is the ultimate goal of the video tutorial?

The ultimate goal is to demonstrate how to calculate a running total by row or by column in Excel, using dynamic arrays and advanced functions like UNIQUE, FILTER, and MAKEARRAY.

How does the scenario begin in the video?

The scenario begins with a simple task of listing items in rows and quarters in columns, followed by calculating a running total by row.

Why does the initial solution work with the 'SUMIFS' function?

The 'SUMIFS' function works initially because the quarters are listed in alphabetical order, which aligns with the intended calculation of the running total by row.

What problem arises when regions replace the quarters in the scenario?

When regions such as North, South, East, and West replace the quarters, the order becomes jumbled alphabetically, causing incorrect calculations for the running total.

How is the problem with the order of regions solved in the video?

The issue is solved by using dynamic array functions like MATCH to determine the positions of rows and columns and then applying FILTER and MAKEARRAY to properly calculate the totals.

What role does the MATCH function play in the solution?

The MATCH function is used to find the position of each row and column in the final array, which helps organize and align the values correctly for the calculations.

What does the 'MAKEARRAY' function do in this tutorial?

'MAKEARRAY' is used to generate a dynamic array of positions for rows and columns, which can then be applied in the FILTER function to calculate the running total across the grid.

Why is the LET function used in the final formula?

The LET function is used to define variables for row names, column names, and positions, which simplifies the formula and makes it more efficient by avoiding repetitive calculations.

How does the solution handle calculating running totals by columns instead of rows?

To calculate running totals by column, the formula is adjusted by changing the row references to column references, thus altering the comparison logic to align with column-based calculations.

What makes this solution 'unbelievable' as mentioned in the video?

The solution is considered 'unbelievable' because it efficiently handles complex dynamic array calculations, providing a clear and flexible way to compute running totals regardless of the order of the items or regions.