Cat On A Spreadsheet

So far in this series, we have used dynamic arrays primarily to transform datasets. We have filtered records, enriched them with reference information, grouped them into reporting categories and calculated measures at different levels of detail. In the previous post, we took another step by using LAMBDA to turn repeated reporting logic into reusable functions. That allowed us to separate business rules from individual report formulas and reuse the same logic in several places. But LAMBDA has another role that is arguably even more interesting.

Several modern Excel functions accept a LAMBDA as an argument and apply it across an array. Rather than defining a reusable function in Name Manager, we can define a calculation directly inside another function and tell Excel how to apply it to our data. This opens up possibilities that are less convenient with traditional worksheet formulas, particularly when calculations need to operate on individual records, complete rows or a sequence of values where each result depends on what came before it.

The functions we will examine are MAP, BYROW, BYCOL, REDUCE and SCAN. Although they are closely related, they solve different problems.  Understanding those differences is more useful than simply learning their syntax.

Applying calculations to individual records with MAP

Let's start with a realistic operational reporting problem. Suppose a service department tracks the number of requests received and completed each day. The information is stored in a table called tblDailyOperations, containing Date, Received and Completed. Management wants a daily performance report showing whether the department processed more requests than it received, whether demand exceeded completed work, or whether there was no net movement.

This might seem like a trivial calculation. Subtracting completed requests from received requests gives us the net movement for each day. And indeed, if that is all we need, tblDailyOperations[Received]-tblDailyOperations[Completed] is already a perfectly good dynamic-array formula. We do not need LAMBDA for ordinary array arithmetic.

But suppose management wants something more descriptive. If the number of completed requests exceeds the number received, the report should display "Backlog reduced". If more requests were received than completed, it should display "Backlog increased". Otherwise, it should display "No change".

We could write an IF formula using the two table columns directly - however, MAP provides another way to express the calculation, one that becomes increasingly useful as the logic grows more complicated.

MAP takes the two arrays and processes their corresponding elements together. For each row, it passes the received and completed counts into the LAMBDA and returns the result. The output contains one value for every input row.

This is the defining characteristic of MAP: it transforms elements of one or more arrays into corresponding results. There is no explicit loop, no helper column and no need to copy the calculation down the worksheet.

An important detail is that the LAMBDA requires one parameter for each input array. Because we supplied two arrays, the calculation has two parameters: Received and Completed. If we added a third array containing daily targets, we would need a third parameter.

This creates a useful way of thinking about the function. MAP is appropriate when the calculation is primarily concerned with corresponding values from several arrays rather than with the entire dataset at once. For example, consider a transaction dataset containing quantities, prices and discount percentages.  We might calculate the net value of every transaction using MAP with three input arrays and a LAMBDA that applies the discount.

Of course, direct array arithmetic would usually be simpler for that particular calculation. The benefit of MAP becomes clearer when different inputs require different treatments, perhaps because the calculation involves several conditions, exceptions or business rules.

The important point is not that MAP can replace ordinary calculations. It is that it gives us an explicit way to apply custom logic element by element. And, because the logic lives inside a LAMBDA, it can be more sophisticated than a simple arithmetic expression.

There is a limitation worth noting: the calculation for each element must produce a single result. MAP cannot be used to return a separate multi-column array for every input element and automatically combine those arrays into a larger table. If we need several calculated outputs, we can use separate array calculations and combine them with HSTACK, or consider a different approach to constructing the resulting dataset. This brings us to a related problem.

Evaluating complete rows with BYROW

MAP works particularly well when we can describe the calculation in terms of corresponding elements. Sometimes, however, we want to evaluate a whole row as a unit.

Imagine that management receives a quarterly report with one row per team and separate columns containing January, February and March performance. The data is stored in tblQuarterlyPerformance, with columns Team, Jan, Feb and Mar. Each team therefore has three monthly performance values. Management is not particularly interested in which month was highest. It wants to identify teams whose performance varies significantly across the quarter.

For this example, we will define significant variation as a difference of more than 20 requests between the highest and lowest monthly values. The condition is straightforward: calculate the maximum value in the row, subtract the minimum value in the same row, and test whether the result exceeds 20. BYROW lets us apply that calculation to every team:

Notice that the LAMBDA has only one parameter, Months. That parameter represents an entire row of the input array. For the first team, Months contains its January, February and March values. For the second team, it contains that team's three values, and so on. Excel evaluates the calculation once for each row and returns a single result for each team. We could combine those results with the team names using HSTACK, producing a compact exception report.

This is fundamentally different from the earlier MAP example. MAP processes corresponding elements of arrays. BYROW processes an entire row at a time. The distinction becomes useful when the calculation naturally operates across several related columns.

For example, BYROW can calculate the maximum value across a series of periods, determine whether any monthly target was missed, count how many measures exceed a threshold or calculate a composite score from several fields. There is no requirement that those fields be physically adjacent in the worksheet. We could first assemble the relevant columns using HSTACK or select them from a wider dataset using CHOOSECOLS.

One limitation is important: the LAMBDA supplied to BYROW must return a single result for each row. It cannot return several independent values that Excel automatically expands into multiple columns. That means BYROW is excellent for producing one measure or classification per record, but it is not a general mechanism for constructing an arbitrary multi-column report from each input row.

There is also a closely related function called BYCOL. As the name suggests, it performs the same kind of calculation by column rather than by row.

In our quarterly performance table, BYROW can evaluate each team's performance across the three months. BYCOL, applied to the same three monthly columns, can calculate a result for each month across all teams. For example, =BYCOL(tblQuarterlyPerformance[[Jan]:[Mar]],LAMBDA(Month,SUM(Month))) returns three totals: one for January, one for February and one for March.

That gives us an interesting contrast. The same input matrix can be examined horizontally to produce team-level measures or vertically to produce period-level measures.

Neither function changes the underlying data. They change the direction in which the calculation is applied. In practical reporting, that can be very useful when working with matrices containing monthly values, departmental measures, product categories or other dimensions arranged across columns. But both BYROW and BYCOL have an important feature in common: each individual calculation is independent of the others. The result for the second row does not depend on what happened in the first row. However, that is not always how business calculations work.

When the current result depends on the previous one

Let's return to our daily service department example. We know how many requests were received and how many were completed each day. Management now wants to know the outstanding backlog at the end of each day.

This is not merely a classification of individual rows. If the department begins with 100 outstanding requests, receives 30 new requests and completes 20, the closing backlog is 110. The next day starts with those 110 outstanding requests. If another 40 arrive and 55 are completed, the closing backlog becomes 95. The calculation for the second day therefore depends on the result of the first.

This kind of calculation is extremely common in business reporting. Cash balances, inventory levels, outstanding orders, accumulated leave entitlements and running budgets all involve values that change over time. Historically, we might implement such a calculation with a series of formulas where each row references the result immediately above it. That works, but it creates a chain of worksheet dependencies. It also requires the formulas to extend correctly as the dataset grows.

SCAN provides a dynamic-array alternative. It processes an array sequentially, maintaining an accumulator whose value is updated as each new element is processed.

The important distinction from the functions we have examined so far is that each step can use the result of the previous step. Suppose our opening backlog is stored in H2. We can calculate the daily net movement as received requests less completed requests. If we then feed that movement into SCAN, the function can calculate the closing backlog after every day.

For simplicity, we are assuming that the table contains one record per day for the department being reported, and that the received and completed counts are numeric. The first stage sorts the data chronologically. That matters because SCAN processes values in their supplied order. An accumulating calculation has no meaningful chronological interpretation if the source records are out of sequence. We then calculate NetMovement, which tells us how much the backlog changed each day.

The SCAN calculation begins with H2, our opening backlog. For the first element, Backlog is the opening balance and Movement is the first day's net movement. The LAMBDA adds them and returns the first closing backlog. That closing backlog becomes the starting accumulator for the next element. The process continues through the entire array. Unlike an ordinary aggregate, however, SCAN returns every intermediate accumulated result. We therefore receive a complete history of closing backlogs rather than just the final number.

This is precisely what makes the function valuable for reporting. We can use those values to create a daily trend, identify periods when the backlog exceeded an acceptable threshold or calculate the maximum outstanding workload during the reporting period.

If H3 contains the maximum acceptable backlog, IF(ClosingBacklog>H3,"Above target","Within target") gives us a classification for every day. That classification can be added to the output using HSTACK. The entire report then responds dynamically to changes in the transaction data, opening backlog or threshold.

There is a subtle but important difference between the result of SCAN and a conventional running-total calculation. For simple addition, the two approaches produce the same numbers. In fact, if all we needed were a final total, summing the net movements and adding the opening balance would be simpler. The real advantage of SCAN is that the accumulation rule does not have to be ordinary addition.

Suppose we are modelling a queue in which outstanding work cannot fall below zero. We might use MAX(0,Backlog+Movement) as the accumulation rule. Now the result of each step depends on a rule applied to the previous balance and the current movement. That cannot generally be reproduced by simply calculating the cumulative sum and replacing negative results with zero afterwards. A floor applied at every step changes the starting point for subsequent steps. We would, however, need to be careful about using that particular rule in a real operational report. A negative calculated backlog could indicate incorrect completion counts, a missing opening balance or another data-quality problem. Silently forcing the balance to zero might hide an issue that deserves investigation.

That is a useful reminder that a technically elegant calculation is not necessarily the correct business calculation. SCAN allows us to express the accumulation rule. We still have to decide what that rule should mean.

REDUCE: returning the final result

REDUCE is closely related to SCAN. Both process an array using an accumulator and a LAMBDA. Both allow each step to depend on the result of the previous step. The difference is in what they return: where SCAN returns the intermediate accumulated results, REDUCE returns the final accumulated result. If we only want to know the backlog at the end of the reporting period, we can use:

=REDUCE(H2,tblDailyOperations[Received]-tblDailyOperations[Completed],LAMBDA(Backlog,Movement,Backlog+Movement))

The result is one number: the final backlog after all the daily movements have been processed. For this simple example, the calculation is equivalent to adding the opening balance to the sum of all movements. There is no reason to prefer REDUCE for ordinary addition alone. The function becomes more valuable when the accumulation involves a rule that cannot be expressed as a simple aggregate.

For example, returning to our hypothetical queue that cannot have a negative balance, the expression

=REDUCE(H2,NetMovement,LAMBDA(Backlog,Movement,MAX(0,Backlog+Movement)))

applies that floor after every movement and returns only the final result. The distinction between REDUCE and SCAN is therefore mainly about the output we need. If management wants to see how a value evolved throughout the period, SCAN is the natural choice. If a calculation only needs the final accumulated state, REDUCE is more appropriate. This is similar to the distinction between a running balance report and a closing balance KPI. They may be based on the same underlying logic, but they serve different reporting purposes.

There is another detail worth noting. Both functions accept an initial value. That initial value should not be treated as an unimportant technical argument. In our example, it represents a genuine business quantity: the outstanding backlog at the beginning of the reporting period. Using zero simply because it makes the formula easier to write would produce a misleading report unless the department actually began with no outstanding work. The same applies to opening cash balances, opening stock and other accumulated measures. The accumulator needs a meaningful starting state. For a calculation that begins halfway through a historical dataset, the starting state may need to be retrieved from an earlier closing balance or calculated from the preceding history. Otherwise, even a perfectly functioning SCAN formula will produce incorrect results.

Using the functions together

Although we have examined these functions individually, they become particularly interesting when combined. Our daily operations report provides a useful example: MAP could classify the relationship between requests received and completed on each day; SCAN could calculate the outstanding backlog after each day; BYROW could evaluate several daily performance measures and return an exception flag; REDUCE could produce a closing balance for a management KPI; BYCOL could summarise measures arranged across a matrix, perhaps where different reporting periods occupy separate columns.

Each function performs a different kind of operation. The key is to select the function that matches the structure of the calculation. For an element-by-element transformation, use MAP when its ability to express custom logic adds value. For a calculation that consumes each row as a unit, consider BYROW.

For the corresponding operation across columns, consider BYCOL. For a sequential calculation that returns the final accumulated result, consider REDUCE. For a sequential calculation that returns the intermediate results, consider SCAN. These functions are not substitutes for SUMIFS, COUNTIFS, FILTER or ordinary array arithmetic. Frequently, those simpler functions remain the better choice. The advantage is that we now have a broader vocabulary for expressing calculations. A requirement that previously led to a helper column, a VBA loop or a particularly complicated nested formula can sometimes be expressed directly in terms of how values should be processed. That is a meaningful extension to Excel's calculation model.

The importance of array shape

There is one concept running through all of these examples that deserves particular attention, and that is that different functions produce different shapes of output. MAP generally returns an array corresponding to the shape of its input arrays, BYROW returns one result for each input row, BYCOL returns one result for each input column, SCAN returns an array of intermediate results and REDUCE returns the final accumulator, which may be a single value or, in supported calculations, another accumulated structure. Understanding these output shapes is essential when combining dynamic-array functions.

Suppose our daily report contains 200 rows. A MAP calculation over those 200 records produces 200 results. A SCAN over the corresponding daily movements also produces 200 results. Those arrays can be placed alongside the source fields using HSTACK because they have compatible dimensions. But if we apply BYCOL to a three-column monthly matrix, the result will contain three values arranged horizontally. We cannot simply treat that as another 200-row field. The calculation may be perfectly correct in isolation while its output shape makes it unsuitable for the next transformation.

This is one of the most common sources of confusion when dynamic-array formulas become complicated. It helps to think of every stage as having both a meaning and a structure. The meaning tells us what the result represents and the structure tells us how many rows and columns it contains. A well-designed dynamic-array formula respects both.

It is also important to remember that Excel does not generally support arbitrary nested arrays in spilled results. A LAMBDA inside MAP or BYROW cannot simply return a different-sized array for each input and expect Excel to assemble those arrays automatically. Where a result must have several columns, we may need to construct each output column separately or use a function specifically designed to produce a rectangular array.

That distinction will become particularly relevant in the next post.

When to use these functions in a real reporting system

As with the other techniques we have explored, there is a difference between what these functions make possible and what makes sense in a production workbook. A MAP calculation involving thousands of records may be perfectly reasonable, particularly if the logic is relatively lightweight. A more complex LAMBDA that repeatedly filters a very large dataset for every individual record can be considerably more expensive.

Likewise, sequential accumulation has unavoidable dependencies. Each new accumulated state depends on the previous one, so the calculation cannot be treated as a collection of entirely independent operations. That does not make SCAN or REDUCE unsuitable for reporting. It means they should be used where sequential logic is genuinely needed rather than simply as more elaborate alternatives to SUM.

There are also maintainability considerations. Some users will recognise SUMIFS immediately but be unfamiliar with MAP or SCAN. A sophisticated formula is not automatically easier to maintain because it contains fewer worksheet cells. For business-critical calculations, named intermediate results, sensible parameter names and clear documentation can make a substantial difference. A function should earn its place by making the calculation clearer, more reliable or more adaptable.

In our daily operations example, SCAN is a natural choice because the requirement is inherently sequential. Each closing backlog becomes the basis for the next day's balance. By contrast, using MAP simply to subtract one column from another would add complexity without delivering much benefit. Choosing the simplest appropriate operation remains a sound principle, even when the formula engine offers more advanced possibilities.

There is also a practical question of compatibility. These functions are available in current Microsoft 365 versions of Excel, although support in perpetual-release versions varies. Workbooks intended for users on older Excel installations should be checked before relying on them. 

A different way to think about worksheet calculations

We began this series by treating dynamic arrays as a way to transform and reshape datasets. As the examples have become more sophisticated, a broader picture has emerged. Modern Excel formulas can do considerably more than return values from cells. They can define datasets, establish reporting populations, derive analytical dimensions, enrich records, aggregate measures, construct reports and apply reusable business rules. With MAP, BYROW and BYCOL, we can describe calculations that operate across individual elements, rows and columns. With REDUCE and SCAN, we can describe calculations that accumulate state as a sequence is processed.

These capabilities bring some familiar programming concepts directly into the worksheet calculation engine, without requiring the workbook to use VBA. The interesting part is not the resemblance to programming itself. It is that the techniques can make a reporting calculation correspond more closely to the business problem it represents: if a measure is calculated independently for every record, we can express it as an element-wise transformation; if a measure depends on all the values in a row, we can express it as a row calculation; and if a balance evolves over time, we can express it as an accumulation. That is often easier to reason about than a collection of intermediate worksheet cells whose relationships are scattered across several sheets. But one important reporting challenge remains.

So far, we have usually begun with an existing dataset and transformed its values or shape. Sometimes, however, the output we need is a completely new matrix. Imagine a management report with departments down the rows, months across the columns and a calculated performance measure at every intersection. A PivotTable can produce that kind of output, of course. But what if the calculation is customised, the reporting periods are generated dynamically and we want the entire matrix to be produced by one formula?

For that, we need a way to generate a rectangular array by defining what should appear at each row and column intersection. That is where MAKEARRAY comes in. In the next post, we will use it to build a fully dynamic management reporting matrix from transaction data, bringing together several of the techniques we have developed throughout this series.

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet