We’ve all encountered it — the “Main Excel Spreadsheet Managing the Business.” The very one B2B companies use to calculate million dollar quotes. It has 12 tabs, 1000+ nested formulas, and zero documentation. For ten years, it had “quick fixes” slapped on and constants hidden away. It’s no longer just a file, but a living organism that no one fully understands except for the guy who quit years ago. That’s how puzzled I was. Moreover, there was uncertainty whether even half of the formulas were needed, or if they were vestiges of the past.
Typical cell:
=IF($D11=$D10,””, IF(ISNUMBER( INDEX(Data!$T$10:$U$17,
MATCH(TabCalc!$F11,Data!$T$10:$T$17,0),2)),
INDEX(Data!$T$10:$U$17, MATCH(TabCalc!$F11,Data!$T$10:$T$17,0),2),
INDEX(TabProd!$C$8:$U$112,TabCalc!$D11,I$1)))
I was tasked with transferring this logic into code so that it was all computed by software. The Excel file seemed to have everything it needed, but in reality — it was a complicated black box. 1069 formulas.
The challenge was in how to translate a thousand interdependent formulas into clean code without losing any edge cases.
Here’s what I ended up doing.
Instead of rewriting everything from scratch at once with uncertain prospects of bug proliferation, I used a strategy of lazy computations and mocks.
I built a structure on Groovy that mimicked Excel’s behavior. Each computation (from a cell) I defined as a function that executed only when it was called. And the functions were a multidimensional dictionary.
I started from the end of the computation graph: from results to inputs. If a formula depended on something I hadn’t yet written, I “mocked” it in the code, simply substituting the value from the Excel sheet.
Bit by bit I replaced these mocks with real logic. Comparing the output of my code to the Excel at each step, I could clearly see where my logic diverged.
In other words, I moved from the result to the input data. At each step, it was clear which mocks needed to be turned into code, and I could compare version +1 with version -1 — the result had to match. As soon as all mocks were replaced with calls — the task was done.
The real “secret ingredient” was the dynamic nature of Groovy for creating a multidimensional map of functions. Instead of static variables, I used a deeply nested structure, where each “leaf” was a closure. This allowed access to any part of the table — be it an input parameter, a config constant, or a complex intermediate result — through a simple, unified syntax, and some components were dynamic.
Here’s an example:
conf[“group”] = { x -> [“a”, “b”, “c”] }
conf[“group”]().each {
calculate[“Group”][“Subgroup”][it][“TotalQuantity”] =
{
x -> calculate[“Group”][“Subgroup”][it][“Someparameter”]() * conf[“someConstant”]()
}
}
Using dynamic keys and closures, I could iterate through product groups or data sets. Since these were dynamic functions, not stored values, the entire system worked like a living graph of dependencies.
Testing was possible right from the start of transferring the formulas. The charm was that you were kind of addressing a cell through syntax like calculate[“Totals”][“A”](), but in reality, you were launching an entire tree of calculations at that moment. And this was incredibly convenient for debugging.
In two weeks, the “Black Box” was transformed into a transparent, modular library with clear logic, which produced exactly the same result as the original table.
P.S. Of course, all the data in all the screenshots are thoroughly obfuscated, or rather, written from scratch for this text.
Hi, I'm Rauf Aliev. 