Porting Excel Formulas to the Web: Floating-Point Traps

I took on a job porting a structural calculator (for designing beams, purlins, rafters, and foundations) from a large Excel file to a web app running entirely on JavaScript. There was just one rule: "The web results must match Excel bit-for-bit," because Excel was the source of truth and the web app was just a port. Visually comparing the results (rounded to three decimal places), every field matched exactly. But when I wrote regression tests comparing full-precision values, failures appeared everywhere — the values differed around 1e-13. I spent half a day tracking this down before realizing the math was fine; it was entirely about floating-point evaluation order, so I wanted to share what I learned.

The sheet names, cell references, and numbers in this article are examples. The approach and code are real.

First: read Excel formulas and values separately, not just what is displayed

A common mistake when porting is opening Excel and copying the displayed numbers as target values. Those numbers have already been formatted (rounded for display), so they are not the actual values stored in the machine. More importantly, they do not tell me what order Excel calculates in. I always use openpyxl to read the file twice: once for the "formulas" and once for the "evaluated values at full precision":

# read_cells.py — อ่าน Excel สองชั้น: สูตร (formula) กับ ค่า (value)
import openpyxl

PATH  = "roof-design.xlsm"   # ไฟล์ต้นทาง (ชื่อสมมติ)
SHEET = "Beam"               # ชีตคาน

wf = openpyxl.load_workbook(PATH, data_only=False)   # ได้ "สูตร" เป็น string
wv = openpyxl.load_workbook(PATH, data_only=True)    # ได้ "ค่าที่ประเมินแล้ว"
sf, sv = wf[SHEET], wv[SHEET]

# แผนที่เซลล์ต้อง map เองก่อน: R8=L(สแปน), E12=w(โหลด), X16=M(โมเมนต์)
for cell in ("R8", "E12", "X16"):
    v = sv[cell].value
    # บังคับ .17g เอง — ถ้าใช้ repr Python มันปัดโชว์ให้สั้นเหมือนกัน (3.888)
    print(f"{cell}: {v:<22.17g} <=  {sf[cell].value}")

The output shows both the full target values and the formulas that reveal the order:

R8:  3.6000000000000001    <=  =Input!D8
E12: 2.3999999999999999    <=  =Input!D12
X16: 3.8879999999999999    <=  =E12*R8^2/8

See the X16 line? The moment for a single-span beam under UDL is M = wL²/8. The actual stored value is 3.8879999999999999, not the displayed 3.89 (even the inputs 3.6 and 2.4 have no exact binary representation). I have to force .17g because even Python's repr rounds the display to the shorter 3.888 — I need to extract every digit of the target value for comparison. The formula =E12*R8^2/8 explicitly tells me that Excel evaluates R8^2 first, then multiplies. That is the key to the first trap.

Trap one: wLL is not equal to w*L^2

The problem is that Excel and JavaScript evaluate "powers" at different points. Excel evaluates ^ as a single unit first, meaning it finishes L^2 = L*L as a group, then multiplies by w, giving w*(L*L). JavaScript has no ^ operator for powers (that is XOR), so most people porting the formula simply write w*L*L, which JS reads from left to right = (w*L)*L — multiplying w by L first, then multiplying by L again. That is a different order from Excel.

Floating-point multiplication is not associative (not associative). (w*L)*L and w*(L*L) round at different points, so their results can differ in the last ULP. Here is a concrete example:

// M = w * L^2 / 8   (โมเมนต์คานช่วงเดียว รับ UDL)
const w = 2.4;   // kN/m
const L = 3.6;   // m

const naive = w * L * L / 8;    // JS อ่านซ้ายไปขวา = ((w*L)*L)/8
const excel = w * (L * L) / 8;  // ครอบ square ให้เป็นหน่วยก่อน = ตรงกับ Excel =w*L^2/8

console.log(naive.toPrecision(20));  // 3.8880000000000003446
console.log(excel.toPrecision(20));  // 3.8879999999999999005
console.log(naive === excel);        // false

A difference of just 4.4e-16 (about one ULP) sounds tiny, but if the target is bit-for-bit, it is a failure: Excel gives 3.8879999999999999, while the naive code gives 3.8880000000000003. The fix is straightforward — wherever Excel uses ^2, I group the square as a single unit: L*L, then multiply by the other terms. Do not let it run left to right.

My rule: whenever Excel raises a term to a power, always put parentheses around the square. Whether it is L², S², arm², a², or Bx²/By² in foundation formulas, every instance must be x*(y*y), not x*y*y.

Trap two: tidying up the algebra breaks the Excel match

This one is worse because it is a well-intentioned change that breaks things. The rafter formula with an overhang a was written in the Excel file as (L+a)^2 * (L-a)^2. While porting it, I carelessly rewrote it to look "cleaner," because mathematically (L+a)²·(L−a)² is exactly equal to (L²−a²)². It is shorter, takes one calculation, and even looks cleverer. But comparing it with Excel revealed a mismatch:

const L = 4.2, a = 0.8;

// รูปที่ Excel เขียนไว้จริง
const asExcel  = (L + a) * (L + a) * ((L - a) * (L - a));  // (L+a)^2 * (L-a)^2

// รูปที่ผมจัดใหม่ให้ "สวย" — เท่ากันทางคณิต แต่คนละ bit
const cleaner  = (L * L - a * a) * (L * L - a * a);        // (L^2 - a^2)^2

console.log(asExcel.toPrecision(20));  // 289.00000000000005684
console.log(cleaner.toPrecision(20));  // 289.00000000000000000
console.log(asExcel === cleaner);      // false

The funny thing is that my rewritten form produced exactly 289, which looked cleaner, while Excel's form produced the messier 289.00000000000006. But Excel is the source of truth — my port has to produce 289.00000000000006, not 289. The lesson is do not rewrite the algebra to match a textbook; write it to match Excel, even if that form looks inferior. Every time I factor an expression, expand parentheses, or move terms across an equation, I change the floating-point rounding order.

Fixtures must use full-precision values, not rounded display values

After fixing those two traps, the web app matched Excel bit-for-bit. Next, I needed regression tests to lock that in so the next refactor would not break it. The critical part of a fixture is the "target value" — if I accidentally copy a displayed value from an Excel cell (such as 3.89) as the target, even correct code returns 3.888, already 0.05% away from 3.89. To make it pass, I might loosen the tolerance to 0.1%. Then a real bug that shifts the value to 3.885 (a difference of 0.08%) slips straight through — effectively making the regression test blind:

// validate.js — เทียบกับค่าเต็มความละเอียดที่ได้จาก openpyxl data_only=True
const beamM = (w, L) => w * (L * L) / 8;   // เวอร์ชันที่แก้ลำดับแล้ว

const fixtures = [
  // way ที่ผิด: ก๊อปค่าที่ตาเห็น "3.89" -> ต้องคลาย tol เป็น 1e-3 -> จับ regression ไม่ได้
  { name: "M ตาเห็น",    excel: 3.89,               got: beamM(2.4, 3.6), tol: 1e-3 },
  // way ที่ถูก: ค่าเต็มจาก data_only=True -> tol แน่นได้ถึง 1e-9 -> refactor เพี้ยนนิดเดียวก็จับได้
  { name: "M เต็มความละเอียด", excel: 3.8879999999999999, got: beamM(2.4, 3.6), tol: 1e-9 },
];

for (const f of fixtures) {
  const rel = Math.abs(f.got - f.excel) / Math.abs(f.excel);
  console.log(`${rel <= f.tol ? "PASS" : "FAIL"}  ${f.name.padEnd(20)} rel=${rel.toExponential(2)}`);
}

The target values I put in must be the full 17-digit values returned by data_only=True, not values rounded by ROUNDDOWN or display formatting. More precise targets allow tighter tolerances and catch regressions sooner. My guiding principle throughout is use full precision during calculation; never round. Keep every digit at every step and round only for the final display (three decimal places are enough for readers). Rounding even once along the way needlessly sacrifices fidelity to the original.

A checklist before calling the port finished

I have condensed what I found into four short points to check for every formula before wrapping up:

1. Always read Excel twice — data_only=False for formulas (to understand the order) and data_only=True for full values (as targets). Do not trust the displayed values.
2. Wherever Excel raises a term to a power, group the square as x*(y*y). Never let x*y*y run left to right.
3. Port the algebra exactly as Excel writes it. Do not factor or tidy up expressions, even if the new form looks "more correct."
4. Use full-precision values from data_only=True in fixtures, set a tight tolerance (1e-9 or tighter), and never round during calculation. Round for display.

I do not see these principles as limited to Excel ports. They are the same rules that apply when moving calculations between languages, compilers, or GPU/CPU. Numbers in a machine are always sensitive to evaluation order. Anyone who needs results to match the original bit-for-bit has to stop thinking "they are mathematically equivalent" and start asking "where does the original round?" If you use this approach, adjust the tolerance and tooling to suit your work. If I have left gaps in the explanation or made any typos, I will come back and fix them later.