Cat On A Spreadsheet

Over the course of this series, we have gradually moved away from treating Excel formulas as isolated calculations. We have used dynamic arrays to filter and reshape datasets, enrich transactions with reference information, group records, calculate measures and construct complete reports. More recently, we have explored LAMBDA as a way to make business logic reusable, and MAKEARRAY as a way to construct reporting matrices whose dimensions are determined by the data rather than by a predefined worksheet layout. These techniques are powerful individually, but their real value becomes apparent when we start combining them.

Consider the reporting matrix from the previous post. It displayed departments down the rows, months across the columns and total transaction value at each intersection. That is useful, but it is still one particular report. What if management wants to compare regions instead of departments? What if the columns should contain product categories rather than months? What if the report needs to display transaction counts or average transaction values instead of totals? We could build separate formulas for every variation. But as the number of possible combinations grows, that approach becomes increasingly difficult to maintain.

A better possibility is to build a small reporting engine. Not an elaborate system that attempts to reproduce Power BI inside a worksheet, but a reusable calculation structure where the user selects the reporting dimensions and measure, and Excel constructs the corresponding output.

The important part is not making the formula as flexible as possible. It is separating the different responsibilities of the report so that each can change without requiring us to rebuild everything else.

Separating the reporting problem into layers

Suppose we have an Excel Table called tblTransactions, containing Date, Department, Region, Category, Status and Value. Each row represents one transaction. The Status column distinguishes posted transactions from pending or cancelled records.

Our management report should include posted transactions only. We also want the user to select a reporting period and control how the resulting matrix is organised.

We will use five control cells. H2 contains any date in the first reporting month, and H3 contains any date in the last reporting month. H4 selects the row dimension, either "Department" or "Region". H5 selects the column dimension, either "Month" or "Category". Finally, H6 selects the measure: "Total Value", "Transactions" or "Average Value". These controls give us twelve possible combinations of dimensions and measures, even before considering different reporting periods.

For example, the user could produce monthly department totals, regional transaction counts by category, or average transaction values by department and month.

All of these outputs have the same basic structure: a row dimension, a column dimension and a measure calculated at every intersection. That is the first useful architectural observation. We do not need twelve different reporting formulas. We need one calculation capable of interpreting the selected dimensions and measure.

There is another distinction to make before we start writing that calculation. The reporting population is not the same thing as the reporting layout. The population consists of all posted transactions within the selected period. That definition should remain consistent regardless of which rows, columns or measure the user chooses. The layout determines how that population is organised and summarised. Keeping these two concerns separate is essential. Otherwise, changing a report's presentation can accidentally change the underlying data being reported.

Constructing the reporting population

We can begin with the part of the calculation that should never change when the user switches dimensions. The reporting period starts on the first day of the month selected in H2 and ends immediately before the first day of the month following H3. We can derive the two reporting months using EOMONTH, then calculate the exclusive upper boundary using EDATE. The inclusion condition is essentially:

(Date>=FirstMonth)*(Date<EndExclusive)*(Status="Posted")

As we have seen in previous posts, multiplying these logical conditions produces an inclusion array. Only transactions satisfying all three conditions are included.

For our reporting engine, we will use that array to filter the source table before doing any grouping or aggregation. This matters because all later calculations will then work with exactly the same reporting population. We can also select only the columns needed by the reporting engine. Rather than depending on the physical order of columns in tblTransactions, we can assemble a small working dataset using HSTACK and explicit structured references. For example, we can combine the Date, Department, Region, Category and Value columns into a five-column array. This gives the calculation a predictable internal structure, even if somebody later moves columns around in the original table.

We now have a working dataset that contains only valid records and only the fields required by our report. From this point onwards, the calculation does not need to interrogate the full transaction table repeatedly. That distinction becomes valuable when we start introducing choices.

Making the dimensions selectable

The first choice is the row dimension. If H4 contains "Department", the report should group records by department. If it contains "Region", it should group them by region.

Assume we have extracted the corresponding columns into arrays named Departments and Regions. The expression IF(H4="Department",Departments,Regions) produces the appropriate grouping keys.

Notice that this does not yet produce the report's row headings. It produces a value for every transaction, identifying the row to which that transaction belongs. If the source contains 5,000 qualifying records, the selected row-key array also contains 5,000 values. We can then apply UNIQUE and SORT to generate the distinct headings. The result might contain Finance, Operations and Sales when Department is selected, or East, North, South and West when Region is selected. The calculation does not need to know in advance how many departments or regions exist.

The column dimension works in much the same way, with one important difference. If H5 contains "Category", we can take the transaction categories and use UNIQUE to establish the distinct column headings. If H5 contains "Month", however, we want to generate the complete calendar range, including months with no transactions. This is the time-intelligence problem we explored earlier. We use EDATE and SEQUENCE to generate every month between the selected start and end months, and we use EOMONTH to assign each transaction to its corresponding month. The column dimension is therefore slightly more sophisticated than the row dimension, but its responsibility remains the same. It provides two things: an array identifying the column to which each transaction belongs, and an array containing the distinct column headings that should appear in the finished report. Once we have these, the measure calculation becomes independent of whether the selected columns represent months or categories. That is a significant advantage, as the aggregation only needs to know which transactions belong to the current row and current column. It does not need to know what those dimensions represent.

Making the measure selectable

The next choice is the measure itself. We will begin with three straightforward measures: "Total Value" means the sum of transaction values at the current intersection, "Transactions" means the number of qualifying transactions, and "Average Value" means the total value divided by the transaction count, calculated using the records at that intersection. These measures are related, so it makes sense to calculate the count and total once and then choose which result to return.

SWITCH is particularly convenient for that. Conceptually, the measure-selection calculation becomes:

SWITCH(H6,"Total Value",TotalValue,"Transactions",TransactionCount,"Average Value",TotalValue/TransactionCount)

We also need to handle intersections containing no transactions. A total value of zero and a transaction count of zero are meaningful. An average transaction value based on zero transactions is not. For that reason, we will return a blank for the average when the transaction count is zero.

There is a useful reporting principle here. The measure should define how a value is calculated. The dimensions should define which records contribute to that value. If those responsibilities remain separate, we can add a new measure without rewriting how the reporting rows and columns are constructed. Likewise, adding a new dimension does not necessarily require changing the measure calculations. That is the foundation of our reporting engine.

Putting the engine together

Let's bring these ideas into one formula. The following version assumes that H2 and H3 contain valid dates, that the selectors use the exact supported values described earlier, and that Department, Region and Category are populated for posted transactions. It validates the chronological order of the reporting period and handles the case where no posted transactions exist in the selected period.

The formula is substantial, but it is worth examining its responsibilities rather than treating it as one large expression. The first section defines the reporting dates and identifies the records that belong in the calculation; the next section creates the working dataset and its component arrays, and then we interpret the selected row and column dimensions. Only after those decisions have been made do we construct the matrix.

For each intersection, MAKEARRAY supplies the row and column positions to its LAMBDA. Those positions are used to retrieve the appropriate reporting keys.  The formula determines which records match both keys, calculates their count and total, and returns the measure selected by the user.

Finally, HSTACK and VSTACK assemble the headings and numerical body. The result is one complete reporting matrix whose shape and contents respond to the controls. There is no separate formula for Department by Month, another for Region by Category, or another for each of the three measures. The control selections determine how the same calculation structure behaves.

One practical detail deserves attention: when Month is selected, the column headings contain genuine Excel dates. Those header cells should be formatted as mmm yyyy. The formula produces the values, while worksheet formatting determines how they appear.

What makes this an engine rather than another complicated formula?

The word engine can sound grander than the result warrants. We have not built a general-purpose reporting platform, and the formula does not support arbitrary dimensions or measures - it only supports a clearly defined set of reporting options.

What makes the design interesting is that the underlying operations are separated according to their responsibilities: the reporting population determines which records are valid, while the dimension selectors determine how the records are grouped, the measure selector determines what is calculated and the output stage determines how the result is arranged. These are all independent decisions.

Suppose management requests a new measure: maximum transaction value. We would not need to rewrite the department or month-selection logic. We would introduce a calculation for the maximum qualifying value and add another option to the measure-selection expression. Similarly, if we wanted to introduce a row dimension called CustomerType, we would extend the working dataset and the row-selection logic. The core MAKEARRAY calculation would still evaluate matching row and column keys.

That is a useful degree of separation, however it is important not to confuse separation of responsibilities with perfect modularity. Our complete formula still contains all those responsibilities in one LET expression. Although its intermediate names make the structure visible, changing the formula is not necessarily convenient. For an initial implementation, that may be acceptable, but for a workbook that will be maintained over time, we can improve the architecture further.

Moving the calculation into a reusable function

The previous post on LAMBDA demonstrated how we can create named functions using Name Manager. We can apply the same principle here. Instead of referring directly to the selector cells throughout the calculation, we could create a named function called BuildReport with parameters representing the first reporting month, last reporting month, row dimension, column dimension and measure. The worksheet would then call it using a formula such as:

=BuildReport(H2,H3,H4,H5,H6)

The function would contain the reporting logic, while the worksheet would contain the controls and the presentation. This would be particularly useful if several worksheets needed to generate reports using the same rules but different control selections.

There is, however, an architectural decision involved. Should BuildReport be responsible for everything, including filtering transactions, generating dimensions, calculating measures and adding headings? For a relatively small workbook, that could be perfectly reasonable, but as the number of supported reporting options grows it may become preferable to separate reusable calculations into smaller functions.

For example, a function responsible for identifying valid transaction records could be used independently of the matrix report. Another function could provide the available reporting dimensions. The main reporting function could then concentrate on combining those components.

There is a balance to strike. Too little separation produces one enormous function that is difficult to test, but too much separation produces a network of named functions whose relationships are difficult to follow. I would favour creating a separate function only when it represents a recognisable operation that is useful independently or likely to change for its own reasons. A named function should have a clear responsibility, not merely exist because part of a larger formula could technically be extracted. That is as true in Excel as it is in conventional software development.

Designing the worksheet around the engine

The calculation itself is only one part of a usable reporting workbook. We also need to think about how the user interacts with it.

A simple design might use one worksheet for the report, with the five controls above the output area. Data Validation dropdowns would restrict the dimension and measure selections to supported values. This is preferable to expecting users to type "Department" or "Average Value" correctly every time. It also means the formula does not need to accommodate dozens of alternative spellings or unexpected input values. The report can update automatically whenever one of the controls changes.

But the worksheet should make clear what the current report represents. A matrix of numbers without context can be misleading, particularly when different users can choose different dimensions and measures. A report showing average transaction values should not look identical to one showing total value without any indication that the measure has changed. It may be useful to include a descriptive report title that reflects the selections. For example, a title could display “Total Value by Department and Month” or “Transaction Count by Region and Category”, depending on the active controls.

The reporting period should also be visible. This is not simply a cosmetic concern; management reports are often copied into emails, presentations or other workbooks. Once detached from the original control cells, a table can lose the context necessary to interpret it correctly. A clear title, reporting period and measure description reduce that risk.

Number formatting also deserves attention. Total values may need currency formatting. Transaction counts should normally be displayed as whole numbers; average values may require decimals. A single static number format applied to the output area may not be appropriate for every measure. If the workbook is intended to remain formula-only, we can keep the format relatively neutral or provide separate presentation areas for different measure types.

For a more polished reporting application, VBA can respond to control changes and apply the appropriate formatting. That does not mean VBA needs to perform the underlying calculations. It can simply handle presentation and user interaction while dynamic arrays remain responsible for the reporting data. This separation can be particularly effective in an Excel-based application. The formula engine performs the calculations. VBA handles behaviours that worksheet formulas are not designed to control conveniently.

The importance of measure definitions

There is another reason not to add new measures too casually. Different measures have different aggregation properties. Total transaction value and transaction count are additive. We can sum individual departments to get a grand total, assuming the groups are mutually exclusive and complete. But Average transaction value is different.

Suppose one department has two transactions averaging €1,000, while another has 100 transactions averaging €100. The average of the two departmental averages is €550. But the actual average across all 102 transactions is approximately €117.65. Those are very different results.

The correct grand average must be calculated using the underlying totals and counts rather than simply averaging the displayed averages. This is why our formula deliberately does not add row and column totals automatically. Doing so for Total Value or Transactions would be straightforward. Doing so for Average Value would require a different calculation.

We could make the reporting engine aware of the aggregation properties of each measure, but that introduces another layer of complexity. For a small number of measures, the logic may be manageable, but for a larger collection of business KPIs, a dedicated measure-definition layer becomes increasingly valuable. That layer would specify not just how a measure is calculated, but how it behaves when aggregated, what units it uses and how missing data should be treated. This starts to resemble the concept of a semantic model found in more specialised reporting systems. We do not need to recreate that entire architecture in Excel. But recognising the issue helps prevent incorrect totals and misleading comparisons.

Data quality and the limits of flexibility

A flexible reporting engine also makes certain data-quality problems more visible. If we group by department, an incorrectly spelled department name may appear as a separate row. If we group by region, missing region values may create an unexpected category. If the source contains inconsistent transaction statuses, records may be included or excluded incorrectly. Our formula assumes the source values are meaningful, it does not clean or validate them. For an exploratory workbook, that may be acceptable. For a production management report, we should be more deliberate.

Reference fields such as departments, regions and categories should ideally come from controlled master data. Transaction statuses should use consistent values. Numeric measures should contain valid numbers. If the reporting engine needs to handle incomplete source data, that should be an explicit requirement rather than something dealt with by wrapping every calculation in IFERROR.

Blank values can mean different things. A blank department might indicate a data-quality problem. A blank transaction value might indicate an incomplete posting. A genuinely empty department-month intersection might simply mean there was no activity. Those situations should not automatically be treated as equivalent.

The reporting calculation becomes more reliable when the source-data rules are defined before the report is constructed. This is another reason to separate the reporting population from the dimensions and measures. The source-data layer should establish which records are valid and how fields are interpreted. The report should not need to reinvent those decisions every time a user changes a selector.

Performance and the cost of flexibility

Our reporting engine is flexible, but flexibility comes at a computational cost. Every cell generated by MAKEARRAY evaluates the relevant intersection criteria. If the report contains ten departments and twelve months, that is 120 calculations. If we expand it to 100 departments and 36 months, the matrix contains 3,600 intersections. Each intersection may need to examine arrays containing thousands of transaction records. At that point, a compact worksheet formula can represent a substantial amount of work.

Using LET helps avoid recalculating the same named expressions within a formula, but it does not eliminate the cost of evaluating each reporting intersection, nor does converting the whole calculation into a named LAMBDA automatically make it faster. The appropriate solution depends on scale. For a moderate dataset and a limited number of interactive reports, the formula-based engine may work very well. For a large transaction history, we may be better off pre-aggregating the data. Power Query could prepare a cleaned dataset or monthly summary before the report is calculated. A PivotTable or Data Model could handle the aggregations. Dynamic arrays could still provide the interactive presentation layer.

That would not represent a failure of the formula-based approach. It would represent a sensible division of work between different technologies. The objective is not to make dynamic arrays perform every possible task. It is to use them where their strengths - flexible transformation, composable calculations and dynamic output construction - provide a practical advantage.

From a reporting workbook to a reporting application

There is an interesting progression here. A conventional workbook might contain a transaction table and several reports maintained individually. A more advanced workbook might use PivotTables or Power Query to reduce the manual work required. Our current approach creates something slightly different. The user interacts with a small set of controls, and the calculation engine constructs the requested output dynamically.

The workbook begins to behave like a small reporting application. That does not mean it needs menus, forms, macros or a complicated interface. In many cases, a few well-designed control cells and a reliable dynamic-array formula are sufficient. But the architecture is becoming more deliberate. We have a source-data layer, where transactions are stored and validated, we have a calculation layer, where reporting populations, dimensions and measures are defined, we have a presentation layer, where the user selects what to see and where the results appear and, if we later decide to add VBA, Power Query or Power BI, we already have a clearer understanding of which responsibilities belong where.

That is perhaps the most valuable lesson from building this small engine. The dynamic-array functions themselves are useful, but the greater benefit comes from changing how we organise reporting calculations. Instead of treating each report as an independent collection of formulas, we begin to see reports as different views of the same underlying data and business rules.

A department-by-month report and a region-by-category report are not entirely separate problems. They are different combinations of dimensions and measures applied to a common reporting population. Once that structure is recognised, it becomes easier to build reports that are consistent, reusable and adaptable.

There is, however, one remaining question that becomes more important as we move towards production use. How do we know that the engine is producing the correct results? A formula can calculate without errors and still be wrong. It may exclude valid records, include cancelled transactions, misinterpret reporting periods or calculate an average incorrectly. And the more flexible the reporting engine becomes, the more combinations we need to consider.

Testing one department-by-month report is not the same as validating every supported combination of dimensions, measures and reporting periods. This is where we need to borrow another idea from software development: systematic testing. In the next post, we will look at how to test complex dynamic-array reporting formulas using controlled datasets, expected results and reconciliation checks, so that we can establish not merely that a report calculates, but that its calculations are correct.

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet