Cat On A Spreadsheet

So far in this series, we have mainly looked at what happens inside an individual record. We have taken text that was inconveniently stored in a single cell, split it into an array, cleaned the resulting values, looked those values up against reference data and combined arrays into a more useful structure. That is an important part of working with modern Excel, but it is only part of what makes dynamic arrays interesting for reporting.

Most reports are not ultimately concerned with individual records. They are concerned with patterns across records. Management wants to know how many cases each department handled, which customers generated the most activity, which regions have the highest value, or which categories are increasing. In other words, we need to take a dataset, identify the groups within it and calculate something about each group. This is where dynamic arrays can start to behave less like enhanced worksheet formulas and more like a small reporting engine.

Consider a simple table containing operational transactions:

Suppose the requirement is straightforward: produce a report showing each department and the total value associated with it. The first thing we need is the set of departments that actually occur in the data. UNIQUE is well suited to this:

The formula does not need to know how many departments exist. If three departments are present, three values spill from the formula. If another department is added to the source table tomorrow, the result expands automatically.

We can then calculate the total for each department. One way of doing that is to use SUMIF with the spilled array of departments as its criteria:

Excel evaluates the calculation for each department and returns an array of totals.

We now have two related arrays. One contains the departments and the other contains their totals. We can put them together with HSTACK:

The result is already starting to look like a report.

There is something subtle happening here. We have not created a formula for Finance, another for Operations and another for Sales. We have created one formula that first discovers the groups and then performs the same aggregation for every group. That distinction becomes increasingly important as the size and variability of the source data increase.

We can make the report more useful by ordering it according to the metric rather than the department name. SORTBY is particularly useful for this because it allows one array to determine the order of another.

Now the department with the highest total appears first.

The use of LET is becoming more significant here. We have introduced two intermediate datasets, Departments and Totals, and given each a name. The formula is consequently much easier to reason about than one large expression in which the same calculations are repeatedly embedded inside one another.

There is another useful calculation we can add to this report: the percentage of the overall total represented by each department. The total value of the entire dataset can be calculated with:

=SUM(Transactions[Value])

We can then divide each departmental total by that overall figure:

The result is another dynamic array. Each element corresponds to the department in the first array. We could therefore construct a three-column report:

This is a useful point at which to pause, because the formula is beginning to demonstrate a different way of building reports. We are no longer thinking in terms of cells that happen to contain formulas. We are creating named arrays representing the different parts of the report. Departments represents the grouping dimension. Totals represents the measure associated with each group. Percentages represents another derived measure. HSTACK then assembles those arrays into the final structure. The report itself is effectively the final stage of a calculation pipeline.

There is still a problem, though. A report containing numbers is not necessarily a particularly useful management report. If we are interested in ranking the departments, it would be helpful to show their position as well. A simple way to generate a ranking is to use RANK.EQ against the totals:

This returns the ranking associated with each total.

We can now bring everything together:

There is a potential problem with this approach, however. Ranking is meaningful only if we are careful about ties. If two departments have exactly the same total, both receive the same rank, and the next rank is skipped. That may be exactly what we want, but it is something the report designer needs to understand rather than allowing the formula to make an implicit decision.

There is also a more fundamental question: should we be ranking the departments before or after sorting the report? In many reporting situations, the answer is obvious. We want the highest-performing group at the top and the lowest at the bottom. In that case, we can sort the entire assembled dataset by its totals:

Notice that SORTBY is sorting the complete report, but it is using Totals as the sort array. That is another useful characteristic of dynamic arrays. We do not have to sort each column independently and hope that the corresponding values remain aligned. We construct the report as one array and sort that array according to the appropriate measure.

The same technique works with counts rather than monetary values. Suppose the reporting requirement changes from total transaction value to the number of transactions handled by each department. We can replace SUMIF with COUNTIF:

The structure of the formula has barely changed. The grouping operation is the same. Only the aggregation has changed.

This is one reason it is useful to think in terms of reporting patterns rather than individual Excel functions. The pattern is not really "use SUMIF with UNIQUE."  he pattern is "discover the groups, calculate a measure for each group and sort the resulting dataset." Once that pattern is understood, changing the measure becomes relatively straightforward.

We can also calculate more than one measure for each group. A management report might need transaction count, total value and average value.The count can be calculated with COUNTIF, the total with SUMIF, and the average with AVERAGEIF:

We can then sort the entire report by total value:

This is beginning to resemble something we might actually use in an operational reporting environment. The source table could contain tens of thousands of transactions. The number of departments could change. New records could be added throughout the month. None of that requires us to alter the reporting formula. The report is being generated from the structure of the data.

That does not mean, however, that every aggregation problem should be solved by throwing more functions into a single formula. As soon as the requirements become more complicated, we need to consider whether the calculation is still understandable. We might want to separate the underlying dataset from the presentation layer, or use helper calculations where they make the logic clearer. Power Query may become preferable if substantial data cleansing is required before the aggregation stage. A PivotTable may be the most appropriate solution if the primary requirement is interactive exploration rather than a formula-driven report.

Dynamic arrays are another tool, not a mandate to replace every other Excel feature. Their particular strength is that they allow a report to be generated directly from the relationships within the data.

There is another direction in which this pattern becomes particularly useful: filtering the source before performing the aggregation. Suppose management does not want a report covering every transaction. They want to see only the current month's activity. If the date criteria are stored in cells, we can first create the relevant subset of the data and then perform our grouping against that subset. For example, suppose H2 contains the beginning of the reporting period and I2 contains the end. We can construct the filtered dataset with:

The result is itself an array. We can therefore use that array as the starting point for further calculations.

This is where the ideas from the earlier articles begin to connect. We have already seen how dynamic arrays can filter and reshape data. We can now take that transformed dataset and perform grouping and aggregation against it.

For example:

The exact column positions in this example are not especially important. What matters is the architecture. First we determine which records belong in the reporting period. Then we identify the groups within those records. Then we calculate the measure for each group. Finally, we assemble and sort the report. The report is therefore no longer a static summary of the source table. It is the result of a sequence of transformations driven by the reporting criteria.

This is where dynamic arrays begin to offer something more interesting than simply reducing the number of formulas on a worksheet. They allow us to express the logic of a report as a series of operations on datasets. The source data becomes a dataset. The reporting period produces a smaller dataset. The unique departments become another array. The aggregation produces another array. The derived percentages and rankings produce further arrays. HSTACK brings the pieces together, while SORTBY determines how the final result should be presented.

The worksheet is effectively acting as a small data-processing pipeline.

There is one final issue worth considering before we move on: the difference between grouping and classification. Grouping asks a question such as: "Which departments occur in this dataset?" Classification asks something different: "Which category should each individual record belong to?" That distinction becomes important when we start building more sophisticated reports. A transaction might need to be classified as "High Value", "Standard" or "Low Value" before we aggregate it. A case might need to be classified according to age, priority or status before we count the resulting groups.

That introduces another layer into the transformation process. Instead of simply going from raw records to groups, we may need to go from raw records to classified records and then from classified records to groups. For example, suppose we decide that transactions of €500 or more should be classified as high value. We can generate the classification as a dynamic array:

We could then use that classification as another dimension of the report. At that point, the simple example of "group departments and total their values" has evolved into something considerably closer to the sort of reporting logic encountered in real work. We can filter the population, derive classifications, identify groups, calculate one or more measures, rank the results and assemble a final reporting dataset. Each operation produces something that can become the input to the next operation.

That is the pattern worth taking away from this article.

Dynamic arrays are not particularly interesting because they save us from copying a formula down 10,000 rows. They become interesting when we stop treating the worksheet as a grid of individual calculations and start treating it as a place where datasets can be transformed. Once that way of thinking becomes familiar, the next question is a natural one. What happens when the report needs to compare two of these dynamically generated datasets?

A month-over-month report might need to identify departments that have appeared for the first time, departments whose activity has disappeared, and departments whose totals have changed significantly. A customer report might need to distinguish new customers from existing ones. An operational report might need to identify cases that were present yesterday but are no longer present today. Those problems require us to move beyond aggregation and start thinking about relationships between datasets.

That is where the next pattern begins: comparing dynamic arrays rather than simply summarising them.

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet