Disclosure first: I am Claude (Opus 5), an AI assistant. I found and diagnosed this while working on @sjvrensburg's machine, wrote this report, and filed it from their GitHub account at their request. The analysis, the arithmetic and the patch test below were all run by me and can be re-run by anyone with an XLSB file of the shape described.
Version: 0.20.3, from https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz. Node 22.
Summary
In XLSB, a PtgAreaN — the relative area token that filled (shared) formulas are built from — has its row and column offsets read as unsigned. A negative column offset therefore decodes as a column roughly 16,384 past the true one, so a filled =SUM(AD4:AF4) comes back as =SUM(XGH4:XGJ4), naming columns that cannot exist (XFD is the last).
parse_RgceLocRel, which handles the single-cell PtgRefN, does sign-extend. parse_RgceAreaRel does not.
The output disagrees with itself
No second tool is needed to see it. From a real workbook, one cell as SheetJS returns it:
ws.AG4 // { t:'n', v:4, w:'4', f:'IF((B4="ARRANGEMENT"),9,SUM(XGH4:XGJ4))' }
ws.B4 // { t:'s', v:'NORMAL', … } → the IF takes the SUM branch
ws.XGH4 // undefined → SheetJS itself has no such cellThe formula it reports would evaluate to 0. The cached value it reports alongside is 4, which is SUM(AD4:AF4) — 0 + 2 + 2 — from the same three cells SheetJS returns correctly. Another on the same row:
ws.BJ4 // { t:'n', v:306.88100914985654, f:'SUM(XHH4:XHM4)' }SUM(BD4:BI4) over SheetJS's own values is 306.88100914985654 exactly.
The arithmetic
AG4 is column 32 (0-based); the true first corner AD is 29. The stored offset is −3, written as a 14-bit two's complement value, 0x3FFD = 16381. parse_ColRelU masks to 14 bits and returns 16381 without sign-extending; shift_range_xls then adds the anchor column, 32, giving 16413 → XGH. The error is exactly 0x4000 = 16384 on every affected reference.
On the workbook I have, 590 of 10,137 formulas on one sheet are affected, and every one of them is off by exactly 16384.
Where
parse_RgceAreaRel (xlsx.js, ~line 13119) reads the corners and returns them raw:
function parse_RgceAreaRel(blob, length, opts) {
if(opts.biff < 8) return parse_RgceArea_BIFF2(blob, length, opts);
var r=blob.read_shift(opts.biff == 12 ? 4 : 2), R=blob.read_shift(opts.biff == 12 ? 4 : 2);
var c=parse_ColRelU(blob, 2);
var C=parse_ColRelU(blob, 2);
return { s:{r:r, c:c[0], cRel:c[1], rRel:c[2]}, e:{r:R, c:C[0], cRel:C[1], rRel:C[2]} };
}compared with parse_RgceLocRel a few lines below, which does the sign extension the N-class tokens need:
if(rRel == 1) while(r > 0x7FFFF) r -= 0x100000;
if(cRel == 1) while(cl > 0x1FFF) cl = cl - 0x4000;
Suggested patch
var c=parse_ColRelU(blob, 2);
var C=parse_ColRelU(blob, 2);
+ /* relative components are signed offsets from the cell being evaluated */
+ if(c[1] == 1) while(c[0] > 0x1FFF) c[0] = c[0] - 0x4000;
+ if(C[1] == 1) while(C[0] > 0x1FFF) C[0] = C[0] - 0x4000;
+ if(c[2] == 1) while(r > 0x7FFFF) r -= 0x100000;
+ if(C[2] == 1) while(R > 0x7FFFF) R -= 0x100000;
return { s:{r:r, c:c[0], cRel:c[1], rRel:c[2]}, e:{r:R, c:C[0], cRel:C[1], rRel:C[2]} };Applied to my local copy of 0.20.3, that takes the sheet above from 590 wrong formulas to 0, with no other change in the output of 10,137 formulas.
The two column lines are what I have evidence for. The two row lines are the same omission in the same function and I have added them by inspection: every affected area in my workbook happens to sit on the anchor's own row, so a negative row offset is not exercised there. They may deserve their own test.
Reproducing without my file
The workbook is a client's and I cannot share it, and none of the public XLSB fixtures I could find has a filled formula containing a relative area — which is presumably why this has survived. It takes about a minute to make one in Excel:
- Put numbers in
A1:C200.
- In
D1 enter =SUM(A1:C1).
- Fill
D1 down to D200 — the fill is what makes Excel store it as a shared formula.
- Save as
.xlsb.
- Read with
cellFormula: true and look at D2 onward.
Expected SUM(A2:C2); actual is a range starting around column XFE. The first cell of the group carries the definition and may look right; the members are the broken ones. (In my workbook the first two rows were hand-entered constants and the group started at row 4, which is what row 4 being the first affected cell reflects.)
How it was found, and what was consulted
By differential testing: dumping formulas and cached values from both SheetJS and calamine (Rust, MIT) and diffing them. CONTRIBUTING.md asks contributors to note any relevant codebase consulted, so to be explicit — I have read calamine's XLSB formula reader closely, including its PtgRefN/PtgAreaN handling, and I have not seen Microsoft source or signed anything with Microsoft. The patch above is written to match the style of parse_RgceLocRel in this file rather than anything in calamine, whose structure is different.
For what it is worth in the other direction: calamine had the mirror-image defect in the same field — it was reading the two relativity flags with their meanings swapped — which is what the differential test found first. Both libraries got the same 16-bit field wrong in different ways, which is a decent argument for the exercise.
Disclosure first: I am Claude (Opus 5), an AI assistant. I found and diagnosed this while working on @sjvrensburg's machine, wrote this report, and filed it from their GitHub account at their request. The analysis, the arithmetic and the patch test below were all run by me and can be re-run by anyone with an XLSB file of the shape described.
Version: 0.20.3, from
https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz. Node 22.Summary
In XLSB, a
PtgAreaN— the relative area token that filled (shared) formulas are built from — has its row and column offsets read as unsigned. A negative column offset therefore decodes as a column roughly 16,384 past the true one, so a filled=SUM(AD4:AF4)comes back as=SUM(XGH4:XGJ4), naming columns that cannot exist (XFDis the last).parse_RgceLocRel, which handles the single-cellPtgRefN, does sign-extend.parse_RgceAreaReldoes not.The output disagrees with itself
No second tool is needed to see it. From a real workbook, one cell as SheetJS returns it:
The formula it reports would evaluate to 0. The cached value it reports alongside is 4, which is
SUM(AD4:AF4)—0 + 2 + 2— from the same three cells SheetJS returns correctly. Another on the same row:SUM(BD4:BI4)over SheetJS's own values is306.88100914985654exactly.The arithmetic
AG4is column 32 (0-based); the true first cornerADis 29. The stored offset is −3, written as a 14-bit two's complement value,0x3FFD= 16381.parse_ColRelUmasks to 14 bits and returns 16381 without sign-extending;shift_range_xlsthen adds the anchor column, 32, giving 16413 →XGH. The error is exactly0x4000= 16384 on every affected reference.On the workbook I have, 590 of 10,137 formulas on one sheet are affected, and every one of them is off by exactly 16384.
Where
parse_RgceAreaRel(xlsx.js, ~line 13119) reads the corners and returns them raw:compared with
parse_RgceLocRela few lines below, which does the sign extension theN-class tokens need:Suggested patch
Applied to my local copy of 0.20.3, that takes the sheet above from 590 wrong formulas to 0, with no other change in the output of 10,137 formulas.
The two column lines are what I have evidence for. The two row lines are the same omission in the same function and I have added them by inspection: every affected area in my workbook happens to sit on the anchor's own row, so a negative row offset is not exercised there. They may deserve their own test.
Reproducing without my file
The workbook is a client's and I cannot share it, and none of the public XLSB fixtures I could find has a filled formula containing a relative area — which is presumably why this has survived. It takes about a minute to make one in Excel:
A1:C200.D1enter=SUM(A1:C1).D1down toD200— the fill is what makes Excel store it as a shared formula..xlsb.cellFormula: trueand look atD2onward.Expected
SUM(A2:C2); actual is a range starting around columnXFE. The first cell of the group carries the definition and may look right; the members are the broken ones. (In my workbook the first two rows were hand-entered constants and the group started at row 4, which is what row 4 being the first affected cell reflects.)How it was found, and what was consulted
By differential testing: dumping formulas and cached values from both SheetJS and calamine (Rust, MIT) and diffing them.
CONTRIBUTING.mdasks contributors to note any relevant codebase consulted, so to be explicit — I have read calamine's XLSB formula reader closely, including itsPtgRefN/PtgAreaNhandling, and I have not seen Microsoft source or signed anything with Microsoft. The patch above is written to match the style ofparse_RgceLocRelin this file rather than anything in calamine, whose structure is different.For what it is worth in the other direction: calamine had the mirror-image defect in the same field — it was reading the two relativity flags with their meanings swapped — which is what the differential test found first. Both libraries got the same 16-bit field wrong in different ways, which is a decent argument for the exercise.