Cat On A Spreadsheet

One of the more dangerous assumptions in spreadsheet development is that a formula must be correct because it produces a result. Excel is generally very good at telling us when a calculation cannot be completed. We recognise errors such as #VALUE!, #REF! and #CALC!, and we have a range of techniques available to identify and resolve them. But Excel cannot tell us whether the result of a perfectly functioning formula is the number the business actually needs. A report might exclude transactions that should have been included. It might assign revenue to the wrong department, compare dates incorrectly or calculate an average using an inappropriate denominator. None of those problems necessarily produces an Excel error.

In the previous post, we built a small reporting engine using dynamic arrays. The user could select reporting dimensions and measures, and Excel would construct the corresponding management report. That flexibility is useful, but it also introduces risk. A formula that supports two row dimensions, two column dimensions and three measures has twelve possible configurations before we even consider different reporting periods or changes in the source data. Testing one configuration and deciding that the engine works is not sufficient. If we want to use this kind of calculation for genuine management reporting, we need to establish that it is correct under the conditions it is expected to handle. That means moving beyond checking whether a formula calculates and beginning to test what it calculates.

Start with data whose results are already known

One of the simplest and most effective ways to test a reporting formula is to construct a small dataset for which we know the correct results independently. This is fundamentally different from testing against a large production extract. If we feed 50,000 transactions into a report and it produces a total of €2,847,392, how do we know that number is correct? We could compare it with a PivotTable or another formula, but if both calculations rely on the same misunderstood business rule, they may agree while still being wrong. A controlled dataset gives us something more useful: expected results that we can establish before executing the report.

Let's return to the transaction structure used in the previous post. Our table, tblTransactions, contains Date, Department, Region, Category, Status and Value. We will populate it with the following twelve records:

These records have been chosen deliberately. They include multiple departments, regions and categories. They cover several months and contain transactions that should be excluded because of their status or date. There is a negative transaction, a zero-value transaction and a transaction recorded with a time component on the last day of February. Each of those details will help us test a different aspect of the calculation.

For the first test, we will select January through March 2026, with Department as the row dimension, Month as the column dimension and Total Value as the measure. Before running our reporting engine, we can calculate the expected result manually. The Sales department has €200 in January: €120 plus €80, plus a zero-value transaction. In February, it has €180, consisting of €200 less the €20 negative adjustment. It has no posted transactions in March.

Support has €50 in January, €220 in February and €30 in March. The cancelled €900 transaction must not contribute to January. The pending €700 transaction must not contribute to March. The April transaction is outside the reporting period.

Our expected report is therefore:

The grand total is €680 and there are nine qualifying transactions. These are not values produced by our reporting formula, they are independently established expectations which makes them suitable for testing. If the engine returns anything different, we have evidence that something requires investigation.

Test the individual intersections, not just the total

The grand total is an important control, but it is not sufficient on its own. Imagine that the reporting engine accidentally assigns €100 from Sales to Support - the overall total would still be €680. A reconciliation that checks only the grand total would pass, even though the department report is wrong. We therefore need to test the results at more than one level.

The first level is the individual intersection - for example, the February Support value must equal €220. We can calculate that amount independently using a conventional SUMIFS formula:

This calculation does not depend on MAKEARRAY, the row and column selectors or the internal dimension arrays of our reporting engine. It asks the underlying source table a direct question: what is the total value of posted Support transactions during February? The answer should be 220.

There is still some common logic between the two calculations, of course. Both must understand what constitutes a valid transaction and reporting month. But we are now using a substantially different implementation to verify the matrix result, which reduces the chance of reproducing the same technical mistake in both calculations. We can repeat this process for a few carefully chosen intersections. I would not necessarily check every cell manually. The purpose of the controlled dataset is to choose a small number of tests that exercise the significant behaviours of the calculation.

For example, February Sales tests whether negative transactions are included correctly. March Sales tests whether an empty intersection returns zero.  February Support tests whether a transaction recorded at 18:30 on the final day of the month is included. These are more informative checks than repeatedly testing several ordinary positive-value transactions. A useful test should have a reason to exist.

Reconcile the complete reporting population

We should also verify that the report includes the correct overall population of transactions. For our January-to-March example, there should be nine posted transactions with a combined value of €680.

These are two different controls: transaction count verifies how many records were included while total value verifies the sum of their monetary values. Both are necessary because monetary totals can sometimes conceal errors in record selection.

Suppose a zero-value transaction is incorrectly omitted. The total value remains unchanged, but the transaction count becomes wrong. That could affect average transaction value or any KPI based on transaction volume. Similarly, incorrectly excluding both a positive and negative transaction might leave the grand total unchanged, depending on their values. Checking both count and value gives us stronger evidence than checking either one alone.

For the Total Value configuration of our reporting engine, we can automate the monetary reconciliation. Assume the dynamic report begins in J10. Its first row contains the column headings, its first column contains the row headings, and the remaining cells contain the calculated measures. Because J10# refers to the entire spilled report, DROP(J10#,1,1) removes the header row and the first column, leaving the numerical matrix. We can compare its sum with an independent calculation against the source table:

If the two totals agree within half a cent, the formula returns "PASS". Otherwise, it returns "FAIL". The tolerance is appropriate for a report using ordinary euro amounts with two decimal places. Reports involving other units or precision requirements may need a different tolerance.

This is a simple reconciliation test, but it is extremely useful. We can run it against different reporting periods and dimension selections. For example, changing from Department by Month to Region by Category should not change the grand total of the selected reporting population. If both dimensions partition the same transactions completely, the grand total should remain €680. The arrangement of the figures changes, but the underlying records do not.

There is one important limitation: this reconciliation is valid for additive measures such as Total Value and Transactions. It is not appropriate for Average Value, because summing averages across the matrix does not produce a meaningful overall average. Our test must respect the mathematics of the measure being tested.

The formula also assumes the report has successfully produced a rectangular matrix. When there are no matching transactions and the reporting engine returns a text message instead, the matrix reconciliation should be treated as not applicable rather than run against that message.

Testing the different dimension combinations

Our reporting engine supports two row dimensions and two column dimensions, which gives us four different reporting layouts. We have already established the expected Department by Month result. Let's now change the selectors to Region by Category. The reporting period remains January through March, and we continue to use Total Value. Our controlled dataset produces the following result:

The grand total remains €680, but the distribution is different, because we are grouping the same transactions according to different attributes. This test does more than confirm that the total is correct. It verifies that the engine actually uses the selected dimensions rather than retaining a previous grouping. East Hardware should be €100 because it contains the €120 January transaction and the negative €20 February adjustment. West Software should be €350 because it contains the €200 Sales transaction and the €150 Support transaction.

The cancelled €900 transaction also belongs to West Software, so this intersection is especially useful for detecting an incorrectly applied status filter. If the report returned €1,250 instead of €350, we would have a strong indication that cancelled transactions were being included.

We should also test the other two combinations: Department by Category and Region by Month. These do not necessarily require entirely new calculations. Their expected results can be derived from the same controlled dataset. What matters is that each supported dimension selection is exercised.

A common programming error is to make a selector work correctly for its default value but fail when the alternative is selected. In our previous reporting engine, for example, the choice between Department and Region is implemented through an IF expression. If the user selects a value other than "Department", the formula currently defaults to Region. That is convenient for a tightly controlled dropdown, but it also means an invalid or misspelled selection might silently produce a valid-looking region report, and testing should expose that behaviour.

The appropriate solution may be to restrict the selection through Data Validation and add explicit validation to the formula itself. The point is that a report can return sensible numbers while interpreting the user's controls incorrectly. That is precisely the kind of defect we want our tests to identify.

Testing measures that behave differently

The three measures supported by our engine are Total Value, Transactions and Average Value. They should not be tested in exactly the same way. For Total Value, we already know the expected overall result is €680; for Transactions, the expected overall result is nine, and we can also verify individual intersections: Sales has three qualifying transactions in January, two in February and none in March, while Support has one in January, two in February and one in March. Those counts produce the following matrix:

The count matrix should have the same dimensions as the value matrix, because the selected reporting population and dimensions have not changed, only the measure has changed.

Average Value requires another level of care. For example, Sales has a February total of €180 across two transactions, giving an average of €90. Support has a February total of €220 across two transactions, giving an average of €110. Across all qualifying transactions, the overall average is €680 divided by nine, or approximately €75.56.

We cannot obtain that overall average by simply averaging the displayed department or month averages. Each intersection may contain a different number of transactions, so the average needs to be weighted by the relevant transaction counts.

There is also the question of empty intersections. Sales has no posted transactions in March, so its March Total Value should be zero, and its Transactions measure should also be zero - but its Average Value is undefined because there are no transactions to average. In our reporting engine, we deliberately return a blank for that situation.

That is an important test. Returning zero for the average would suggest that the average transaction value was €0, which is not the same as having no transactions. Returning an Excel division error would expose the mathematical problem but would not provide the intended report presentation. The correct behaviour depends on the agreed measure definition. Once that definition exists, testing should verify it explicitly.

Boundary conditions deserve disproportionate attention

Our controlled dataset contains a transaction recorded at 18:30 on 28 February 2026, and that record has been included for a specific reason. Date boundaries are a common source of reporting errors, particularly when source systems record both dates and times. If a formula checks whether a transaction date is less than or equal to 28 February 2026, it may exclude transactions recorded later during that day because the underlying Excel value contains a fractional time component.

Our reporting engine uses an exclusive upper boundary: dates must be earlier than the first day of the following month, and that includes every valid time on the final day. The controlled dataset allows us to verify this behaviour. If the February Support total is €150 rather than €220, the missing €70 transaction is an immediate clue that the upper date boundary may be wrong.

A similar issue occurs at the beginning of a reporting period. Transactions exactly at midnight on the first day should normally be included, while transactions one moment before the start should not. Testing both sides of a boundary is often more informative than testing dates comfortably inside the period.

The same principle applies to other criteria. If a minimum transaction value is €1,000, we should test a transaction worth exactly €1,000, one slightly below the threshold and one slightly above it; if a report supports a maximum number of displayed periods, we should test the limit rather than only ordinary selections.

Boundary tests are useful because small differences in comparison operators can change the result while leaving the formula syntactically correct.

Testing how the report responds to change

So far, our tests have relied on comparing the report with known results. There is another useful technique: make a controlled change to the source and check how the report responds.

Suppose we add a new posted Sales transaction worth €40, dated 10 February 2026, with Region East and Category Hardware. We should know exactly what will happen: the Sales February Total Value should increase from €180 to €220, the overall total should increase from €680 to €720, the total transaction count should increase from nine to ten, the East Hardware total should increase from €100 to €140, while other intersections that do not contain the new transaction should remain unchanged.

This is a particularly powerful form of testing because it checks the relationship between the source and the report. We are not simply asking whether a particular number is correct. We are checking whether a known change produces the expected consequences. After testing, we can remove the additional record to restore the original controlled dataset.

We can perform a similar test by changing a posted transaction to Cancelled. The transaction should disappear from the qualifying population. The relevant intersection should decrease by its value, and the transaction count should decrease by one.

Another useful test is to reorder the source records. A department-by-month report should not depend on the physical order of transactions in the source table. If we sort tblTransactions by Value rather than Date, the resulting summary should remain unchanged. If it does not, we may have accidentally introduced a positional dependency where a key-based calculation was required. That kind of error can be difficult to spot in ordinary testing because the initial source order may happen to produce plausible results.

These tests help establish general properties of the reporting engine. Adding a qualifying record should affect the appropriate intersection. Excluding a record should remove its contribution. Reordering records should not change grouped results. Such properties can be tested even when we do not manually calculate every value in a large dataset.

Distinguishing no data from invalid inputs

Our reporting engine also needs to handle situations where no report can be produced. Those situations are not necessarily errors in the same sense. If the user selects May through June 2026 in our controlled dataset, there are no posted transactions in that period and the appropriate result is the message "No posted transactions in period". That is a valid reporting outcome. If the user selects March as the starting month and January as the ending month, however, the reporting period is invalid and the formula should return "Invalid reporting period" rather than pretending that no transactions matched. If the user supplies a text value where a date is required, that represents another kind of invalid input. These cases should be tested separately because they have different meanings.

There is also a practical issue with spreadsheet error handling. A report that fails with #SPILL! may be perfectly correct mathematically. The problem could simply be that another value occupies part of the required output range. By contrast, a report that returns no error but silently treats an invalid selector as another valid option may be incorrect even though the worksheet appears to be functioning normally.

Testing therefore needs to consider both the calculation and its environment. We should verify that the spill range is clear, that the expected controls are populated, that the source table contains the required fields and that the result can expand when new departments or periods are introduced. These are not all formula-logic tests, but they are essential to the reliability of the workbook.

Building a dedicated testing worksheet

For a reporting engine that we expect to maintain, I would strongly favour keeping a dedicated test worksheet. It does not need to be elaborate, it can contain a controlled set of inputs, the expected results and formulas that compare the expected values with the actual outputs. A simple test record might identify the scenario, the selected controls, the expected result, the actual result and whether the comparison passed.

For example, one test might verify that February Support totals €220. Another might verify the nine qualifying transactions. Another could check that the Region by Category report has a grand total of €680. Tests involving Average Value would use the appropriate expected averages and numerical tolerances. We can then calculate an overall testing status based on whether the individual checks pass.

The important part is that the expected values should not simply be calculated using the same expression as the report. If our test repeats the production calculation exactly, it may repeat the same mistake. For controlled scenarios, manually established expected results are particularly valuable. For larger data reconciliations, independently constructed SUMIFS, COUNTIFS or PivotTable calculations can provide useful additional evidence.

The testing worksheet should also be separated from the production source data. Otherwise, somebody refreshing the transaction table could overwrite the controlled records or make the expected results obsolete. In a more mature workbook, we might preserve the test data in a separate table and configure the reporting calculation to accept that table as a parameter. That would let us test the same calculation logic without temporarily replacing the actual transaction source.

For the relatively small engine developed in this series, a separate test workbook containing a controlled copy of the calculation may be sufficient. The key is to preserve a known, repeatable testing environment. If a report produces the expected results today, we should be able to repeat the same test after modifying the formula next month. That is what makes the test useful for maintenance rather than merely for initial development.

Regression testing after a change

Suppose management introduces a new measure or asks us to add Customer Type as another reporting dimension. We modify the reporting engine, test the new feature and confirm that it works. Are we finished? Not necessarily. The changes may have affected existing behaviour. Perhaps the new dimension-selection logic accidentally alters the previous Region option. Perhaps a change to the reporting population affects the transaction count. Perhaps a new average calculation introduces errors in empty intersections.

This is where regression testing becomes important. Regression testing means rerunning previously established tests after a change to verify that existing behaviour still works. Our controlled dataset is particularly useful for this purpose. We already know the correct Department by Month totals, Region by Category totals, transaction counts and average values. We can run the same checks after every material change to the calculation, and if a previously passing test now fails, we have evidence that the modification affected something beyond its intended scope. That does not automatically mean the change is wrong. Business requirements sometimes change legitimately, and an expected result may need to be updated, but that update should be deliberate.

A useful practice is to distinguish between changes to the formula implementation and changes to the business rules. If we refactor a formula for readability or performance without changing its intended behaviour, the existing tests should continue to pass. If management changes the definition of a valid transaction, some expected results may need to change. In that case, the tests themselves should be reviewed against the new agreed rule. This makes the testing process part of maintaining the reporting requirements rather than merely checking Excel syntax.

Reconciliation is necessary, but not proof of correctness

There is one limitation worth emphasising: even a report that passes all its reconciliation checks may still be wrong.

Suppose the business rule states that refunds should be included as negative values, but both the reporting formula and the reconciliation formula exclude them. The two calculations may agree perfectly, but the problem is that both have implemented the same incorrect interpretation. Likewise, a grand-total reconciliation cannot necessarily detect incorrect allocation between departments. And a correctly calculated report based on incomplete source data will still produce incomplete results.

This is why I would distinguish between three kinds of validation. The first is calculation validation: does the formula return the expected results for known inputs? The second is reconciliation: are the overall totals and counts consistent with the source data? The third is business-rule validation: do the criteria and measures actually correspond to what management intends to report?

All three are necessary for a trustworthy reporting system. The first two can be automated to a considerable extent; the third requires clear requirements and, where appropriate, review by somebody who understands the underlying business process. Excel cannot decide whether a cancelled invoice should contribute to revenue - it can only implement the rule we give it. The same applies to the treatment of credits, missing departments, incomplete months and unusual transactions.

A reporting formula can be technically flawless while answering the wrong question. That is why testing should begin with the intended meaning of the report, not simply with its implementation.

Performance is also something we can test

Correctness is the first requirement, but it is not the only one. Our dynamic reporting engine uses MAKEARRAY to calculate each intersection of a reporting matrix. As the number of dimensions and source records grows, the calculation may become increasingly expensive. A formula that performs well with twelve controlled transactions tells us very little about how it will behave with 100,000 records and a matrix containing several thousand intersections. We therefore need performance tests using realistic data volumes.

The controlled dataset remains valuable for correctness testing, while a larger representative dataset can be used to measure recalculation behaviour. We should observe what happens when the reporting period changes, when a new department appears, when the measure selector changes and when the source table grows. The relevant question is not only whether the calculation completes, but whether it remains responsive enough for the intended users. If the report takes several seconds to update after every selection, it may still be acceptable for a monthly finance workbook. It may be less acceptable for an interactive operational dashboard that users expect to respond immediately. Performance requirements depend on how the workbook is used.

We should also avoid assuming that a formula must be efficient simply because it occupies one cell. A single dynamic-array formula can perform more work than thousands of traditional worksheet formulas, depending on its design. Testing against realistic volumes is the only reliable way to understand that cost.

From a working report to a reliable report

The reporting engine we developed in the previous post illustrates how far Excel's formula capabilities have evolved. A single calculation can now interpret user selections, construct reporting dimensions, generate a matrix and calculate different measures without requiring us to maintain a separate worksheet structure for every report. But flexibility introduces complexity, and complexity introduces opportunities for error.

The solution is not to abandon sophisticated formulas. It is to treat them with the same discipline we would apply to other important calculations. We should know what the report is supposed to return; we should test it with controlled inputs and independently established results; we should verify both individual intersections and overall reconciliations; we should deliberately test negative values, zero values, empty periods, invalid selections and reporting boundaries; and when we modify the calculation, we should rerun those tests rather than assuming that previously working behaviour remains unchanged.

Perhaps the most important change is conceptual. A working formula is not the same as a validated report; a working formula tells us that Excel can execute the calculation. A validated report gives us evidence that the calculation implements the intended business rules correctly. That distinction matters enormously when the output is used for financial reporting, operational decisions or management performance reviews.

It also brings us to a natural conclusion for this part of the series. We have examined how dynamic arrays can transform data, implement reporting logic, generate outputs and support reusable calculations. We have now considered how to verify that those calculations behave correctly. The next step is to turn these techniques into something that can be used and maintained reliably. That means looking beyond individual formulas and considering the complete workbook: how source data is refreshed, how users interact with reporting controls, how errors and incomplete data are presented, how recalculation is managed and how the finished reports are prepared for distribution.

In the next post, we will bring those elements together and examine how to turn an advanced dynamic-array workbook into a maintainable reporting solution, without unnecessarily complicating it or sacrificing the flexibility that makes Excel useful in the first place.

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet