Reverse Engineering Spreadsheet Constants into Formulas

Someone sent me a spreadsheet to look over. Their team had used it for years to compile figures for routine work. The task was to write a tool to check whether each sheet calculated correctly. On my first pass, I immediately found something odd: a criteria table sat beside the calculation area, neatly laid out, but not a single cell in the sheet referenced it. No VLOOKUP, no INDEX, nothing at all. People read the table by eye and typed the numbers into cells themselves.

What made me sit down and write this article was that the manually entered values did not match the table in the very same sheet. And those two mismatched values flipped the conclusion for the entire sheet. This article is purely about the working method: separating formula cells from manually entered cells, then working out which formula produced a table of constants whose origin nobody remembers. I made up all the numbers in this article as examples. None come from anyone's actual work.

First, distinguish formula cells from manually entered numbers

On screen, the two look exactly alike, but inside the file they are entirely different. An xlsx file is a compressed archive containing XML. A formula cell has an <f> tag storing the formula, along with a <v> tag storing its last calculated result. A cell where someone typed a number has only <v>.

unzip -l book.xlsx | head
# xl/workbook.xml          รายชื่อชีตกับลำดับ
# xl/worksheets/sheet1.xml ตัวเซลล์จริง
# xl/sharedStrings.xml     ข้อความทั้งไฟล์ เก็บรวมไว้ที่เดียว

unzip -p book.xlsx xl/worksheets/sheet1.xml | tr '>' '>\n' | grep -A2 'r="C19"'

I read it using only the standard library, without installing anything extra, because the target machine where this tool would run did not allow package installation. This job only needed two things anyway: reading formulas and reading cached values.

import zipfile, re
import xml.etree.ElementTree as ET

NS = "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}"

def read_sheet(path, sheet="xl/worksheets/sheet1.xml"):
    """คืน dict ของเซลล์ -> (สูตร, ค่าที่แคชไว้, ชนิด)"""
    with zipfile.ZipFile(path) as z:
        shared = []
        if "xl/sharedStrings.xml" in z.namelist():
            root = ET.fromstring(z.read("xl/sharedStrings.xml"))
            shared = ["".join(t.text or "" for t in si.iter(NS + "t"))
                      for si in root.findall(NS + "si")]
        root = ET.fromstring(z.read(sheet))

    cells = {}
    for c in root.iter(NS + "c"):
        ref = c.get("r")
        f = c.find(NS + "f")
        v = c.find(NS + "v")
        val = v.text if v is not None else None
        if c.get("t") == "s" and val is not None:      # ข้อความอยู่ในตารางรวม
            val = shared[int(val)]
        cells[ref] = (f.text if f is not None else None, val,
                      "formula" if f is not None else "typed")
    return cells

Once I had read the whole sheet, the first thing I did was print out which important result cells were of type typed. The list was much shorter than I expected, and every cell on it was a point that needed a check.

$ python audit.py sheet.xlsx --list-typed
C16  typed   60            <- ควรมาจากตารางเกณฑ์ช่วงที่ 2
C20  typed   132           <- ควรมาจากตารางตัวคูณ แถวปีที่ 18
H12  typed   1250          <- ควรมาจาก H12 = G12 * F12
C21  formula =C19*C20
C19  formula =C17-C18
C17  formula =C15*C16/100

Trace the calculation chain backward from the final cell to its inputs

Before checking anything, I needed to know what fed into the final cell. I traced backward from the last cell, then wrote the chain on a single line so people could follow it.

ยอดรวมต่อรอบ   = ผลบวกรายการฝั่งซ้าย - ผลบวกรายการฝั่งขวา     [C15 = SUM(C9:C13) - SUM(E9:E19)]
ฐานคิด         = ยอดรวมต่อรอบ x เปอร์เซ็นต์ตามช่วง            [C17 = C15*C16/100]
ภาระต่อรอบ     = ผลรวมรายการย่อยทุกบรรทัด                    [C18 = H27 = SUM(H9:H26)]
ส่วนที่เหลือ    = ฐานคิด - ภาระต่อรอบ                         [C19 = C17-C18]
มูลค่ารวม      = ส่วนที่เหลือ x ตัวคูณตามจำนวนปี              [C21 = C19*C20]

Written this way, it is immediately clear that the whole chain has two manually entered cells, C16 and C20, and both are multipliers, not additive terms. An error in a multiplier therefore does not just add to the result; it scales the entire result proportionally.

Another thing I found while tracing the chain was a cell whose column heading said "All items excluded," but whose formula was (C17-SUM(H9:H11))*C20. That meant it still kept the first three rows, rather than excluding everything as the heading claimed. This was not anyone's fault. It was a range selected for one particular case, then copied repeatedly until it became a template. When writing a replacement tool, I need to make the excluded rows an explicit list, rather than embed a cell range in the formula.

Reconstruct a single formula from 16 multiplier values

The multiplier table in the sheet had 16 rows and looked like this. No explanation, no source, just numbers.

ปีที่เหลือ  ตัวคูณ      ปีที่เหลือ  ตัวคูณ
   10        90           18        132
   11        96           19        136
   12       102           20        140
   13       108           21        143
   14       113           22        146
   15       119           23        150
   16       123           24        152
   17       128           25        155

First, I looked at its shape by finding the differences between rows: 6, 6, 6, 5, 6, 4, 5, 4, 4, 4, 3, 3, 4, 2, 3. The differences kept shrinking, so it was not linear. This kind of shrinkage is the signature of a geometric series, not a polynomial. For work that multiplies a periodic amount into a total, the most plausible candidate was the present value annuity factor, a standard formula that fits on one page.

factor = [1 - (1 + i)^-n] / i

i = อัตราต่องวด
n = จำนวนงวดทั้งหมด

There were two unknowns: i and how to convert the numbers in the left column into n. Instead of sitting there guessing, I searched both together and let the program tell me which pair matched best.

def annuity(i, n):
    return (1 - (1 + i) ** -n) / i

table = {10: 90, 11: 96, 12: 102, 13: 108, 14: 113, 15: 119, 16: 123, 17: 128,
         18: 132, 19: 136, 20: 140, 21: 143, 22: 146, 23: 150, 24: 152, 25: 155}

best = None
for bp in range(1, 201):                       # อัตราต่อเดือน 0.01% ถึง 2.00%
    i = bp / 10000.0
    for per_year in (1, 12):                   # นับเป็นปีหรือเป็นเดือน
        err = max(abs(annuity(i, y * per_year) - v) for y, v in table.items())
        hit = sum(round(annuity(i, y * per_year)) == v for y, v in table.items())
        if best is None or (hit, -err) > (best[0], -best[1]):
            best = (hit, err, i, per_year)

hit, err, i, per_year = best
print(f"ตรง {hit}/{len(table)} แถว · i={i*100:.3f}% ต่องวด · n = ปี x {per_year} "
      f"· ส่วนต่างสูงสุด {err:.2f}")

The result was i equal to half a percent per month, with n equal to the number of years multiplied by twelve. It matched all 16 rows when rounded to integers. The maximum difference before rounding was half a unit. Once I knew that, the table went from unexplained numbers to something I could explain in full. More importantly, I could extend it beyond its range.

One plausible formula is not an answer yet: try to disprove it first

This was where I was most cautious, because with sixteen numbers and a formula with two free parameters, finding something that roughly fits is not hard. Without deciding in advance what counts as a match, it is very easy to fool myself.

I used three criteria. First, every row must match when rounded to an integer, not just nearly all of them. Second, the difference before rounding must not exceed half a unit in any row. If any row exceeds that, the model is still wrong. Third, the parameters must be numbers someone would actually choose. Half a percent per month is something people might say in a meeting. But if the search returns something like 0.4873 percent, I should suspect that I am bending a formula to fit the data, rather than finding the formula people actually used.

# ก่อนจะเชื่อ ลองรูปแบบง่ายกว่าด้วย ถ้ามันอธิบายได้พอกันแปลว่ายังไม่ได้คำตอบ
import statistics

ys = sorted(table)
vs = [table[y] for y in ys]
lin = statistics.linear_regression(ys, vs)          # เส้นตรง
lin_err = max(abs(lin.slope * y + lin.intercept - table[y]) for y in ys)
ann_err = max(abs(annuity(0.005, y * 12) - table[y]) for y in ys)
print(f"เส้นตรง ส่วนต่างสูงสุด {lin_err:.2f} · สูตรเงินรายงวด {ann_err:.2f}")
# เส้นตรง ส่วนต่างสูงสุด 5.31 · สูตรเงินรายงวด 0.50

Five units and half a unit differ by a factor of ten. That is clear enough to make a decision. But if two candidates produced similar results, I would not claim to have found it. I would state plainly in the documentation that the origin was still unknown. That is better than putting a guessed formula into the tool and having someone use it later as though it had been confirmed.

The table contains rounded numbers: use it within range, and warn when using the formula outside it

This is something programmers often get wrong. Once I have reconstructed a formula, it is tempting to feel that the formula is more precise and use it everywhere. That is wrong, because every result the team previously issued was calculated from the table. Switching to the formula would immediately change the results of existing cases without anyone requesting it.

So I wrote these rules into the tool.

Writing it this way lets the tool do two things at once: reproduce the original results exactly for cases within range, and keep working when it encounters a case outside the range.

The checker must calculate from the entered values and report differences, not fix them itself

While designing it, I debated for a while whether the tool should correct the values automatically. I decided against it, because I do not know whether a manually entered value is a mistake or a decision. If the tool corrects it automatically, I lose the information that someone chose to depart from the table. That information is even more valuable than the correct number.

This is what I had it print.

$ python audit.py review sheet.xlsx

ช่องพิมพ์มือที่ไม่ตรงตารางในชีตเอง
  C16  กรอก 70    ตารางช่วงนี้ให้ 60    -> ฐานคิด 37,964 แทนที่จะเป็น 32,541
  C20  กรอก 165   ตารางปีที่ 19 ให้ 136 -> ตัวคูณสูงกว่าเกณฑ์ 21%

ผลรวมที่ไม่ตรง
  H27  ในชีต 35,063.74   คำนวณใหม่ได้ 35,064.62   ส่วนต่าง 0.88

ถ้าคิดตามตารางทั้งหมด
  ส่วนที่เหลือ = 32,541 - 35,064.62 = -2,523.62  (ติดลบ)

สรุป: ค่าที่กรอกให้ผลเป็นบวก ค่าตามตารางให้ผลติดลบ ต้องให้คนตัดสิน

I gave the checker seven checks to run. Each compares two sides and reports the difference. None overwrites a value in the file.

1. Every manually entered cell that should come from a table Compare it with the table value, then show what the result would be using that value.
2. Every total cell Sum the individual items again and compare with the cached value in the sheet.
3. Items calculated from percentages Recalculate from each row's base and compare row by row.
4. Values outside the table's range Calculate from the formula, but label them every time.
5. Negative results Report plainly that the condition was not met. Never clamp them to zero.
6. Formulas whose column headings do not match their selected ranges Report them as ad hoc selections, not rules.
7. Cells that used to contain formulas but now contain numbers Compare with the template to see the original cell type.

That last one happens more often than I expected. Someone copies and pastes values only over cells that used to contain formulas. Nothing on screen changes at all, but from that moment on, those cells no longer update with their inputs. Comparing cell types against the template is the only way to catch it.

What cannot be reconstructed must be explicitly marked as unconfirmed

I built the tool from a small number of sample sheets, which means there is still plenty I do not actually know. So I added a section to the documentation listing unconfirmed points, and printed that list at the end of the report on every run. I did not hide it in a file nobody opens.

ยังไม่ยืนยัน (ถอดจากตัวอย่างจำนวนน้อย ต้องถามคนตั้งเกณฑ์)
 1. เปอร์เซ็นต์ตามช่วง อิงฐานตัวไหน ตัวอย่างที่มีทุกตัวตกช่วงเดียวกันหมด แยกไม่ออก
 2. ทำไมใบนี้ถึงใช้ค่าที่สูงกว่าตาราง เกณฑ์ต่างจริงหรือหยิบผิดแถว
 3. อัตราต่องวดกับจำนวนปีตั้งต้น เป็นค่ามาตรฐานหรือปรับตามงาน
 4. นอกช่วง 10 ถึง 25 ปี ทีมใช้อะไร ตอนนี้เครื่องมือคิดจากสูตรแล้วติดป้ายไว้
 5. หมายเหตุข้างรายการย่อย มีผลต่อการตัดสินไหม ในชีตไม่เข้าสูตรเลย

The first point is a good example of a limitation the program cannot resolve on its own. If every available sample produces the same value regardless of which base is used, I cannot distinguish which base the criteria refer to. I have to wait for a case where the values differ, or for someone to answer. Guessing and embedding that guess in code is more dangerous than leaving it blank, because when it turns out to be wrong, nobody will know it was a guess.

Keep real numbers from the files out of tests and the tool

These files are often full of other people's data. I therefore set a rule from the start: real files belong in only one place, a working folder excluded from version control. All tests use files I create myself.

Creating test files is easy, because xlsx is just zip. I can write minimal XML and zip it myself, without installing any libraries.

import zipfile

SHEET = """<?xml version="1.0" encoding="UTF-8"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<sheetData>
 <row r="15"><c r="C15"><f>SUM(C9:C13)-SUM(E9:E19)</f><v>54235</v></c></row>
 <row r="16"><c r="C16"><v>70</v></c></row>
 <row r="17"><c r="C17"><f>C15*C16/100</f><v>37964.5</v></c></row>
 <row r="20"><c r="C20"><v>165</v></c></row>
</sheetData></worksheet>"""

def make_fixture(path):
    with zipfile.ZipFile(path, "w") as z:
        z.writestr("xl/worksheets/sheet1.xml", SHEET)

# เทสต์ที่ต้องมีอย่างน้อยสามข้อ
# 1) ป้อนค่าที่พิมพ์มือเดิม ต้องได้ผลเท่ากับที่ชีตแสดงอยู่เป๊ะ
# 2) ป้อนค่าตามตาราง ต้องได้ผลติดลบและรายงานว่าไม่ผ่าน
# 3) ตารางตัวคูณ ปีที่ 10 ต้องได้ 90 และปีที่ 25 ต้องได้ 155 จากตาราง ไม่ใช่จากสูตร

The third test is more important than it looks, because it prevents someone from switching everything to the formula in the future with good intentions. If someone makes that change and the test fails, they will find out before the results of old cases shift.

Personally, I think this kind of reverse engineering of constants is always worth the time. Not because it produces a neat one-line formula, but because afterward I can say whether the numbers in the table are right or wrong, and what to do outside the table's range. Before that, nobody on the team could answer either question. Feel free to use this parameter search method on other tables of constants. If I have left anything out or made a typo, I will come back and correct it later.