OFFSET past the edge of the sheet errors differently in LibreOffice
OFFSET is the engine behind a lot of dynamic ranges, rolling windows and
scroll-through dashboards, and the one thing all of those have in common is that sooner or later the
offset runs off the edge of the sheet. Microsoft is explicit about what happens then: “If rows
and cols offset reference over the edge of the worksheet, OFFSET returns the #REF! error
value.” LibreOffice Calc returns #VALUE! instead.
The surprise
Everyday OFFSET is portable. Moving to a cell, building a height × width
range and summing it, and a zero-offset self-reference all produced identical results in our runs.
Only =OFFSET(A1,-1,0) — the off-the-edge case — disagrees, and it
disagrees about which error to raise, not whether to raise one.
Executed results
Setups per row: B3 = 99 for the first row; A2:A4 = 10, 20, 30 for the
second; B2 = 5 for the last.
| Formula | Excel (documented) | Google Sheets | LibreOffice Calc 25.8.7.3 (executed) |
|---|---|---|---|
| =OFFSET(A1,-1,0) | #REF! | Not yet executed | #VALUE! |
| =OFFSET(A1,2,1) | 99 | Not yet executed | 99 |
| =SUM(OFFSET(A1,1,0,3,1)) | 60 | Not yet executed | 60 |
| =OFFSET(B2,0,0) | 5 | Not yet executed | 5 |
The Excel column is the documented-expected value from our test corpus, sourced from Microsoft's
OFFSET reference page; we did not run Excel. The LibreOffice column is what our harness
computed in LibreOffice Calc 25.8.7.3. Three of the four cases match exactly, which is the useful
headline: OFFSET itself is not a porting hazard, its failure mode is.
Consistent across LibreOffice versions
=OFFSET(A1,-1,0) returned #VALUE! in 24.2.0.3, 24.8.7.2, 25.2.0.3 and
25.8.7.3 alike, and the three well-behaved cases returned 99, 60 and
5 in every build. Nothing changed between releases here.
Why it happens
Excel classifies an offset that lands outside the sheet as a reference failure, so the cell shows
#REF!. LibreOffice classifies the negative row offset as an argument that is out of its
allowed range, and out-of-range arguments in LibreOffice surface as #VALUE! — the
same treatment it gives an out-of-range lookup index (see
VLOOKUP with a bad column index)
and out-of-domain maths such as =SQRT(-16). The consequence is worth stating plainly:
#REF! is much rarer in LibreOffice than a formula author from Excel expects,
so error handling keyed to it quietly stops firing.
How to migrate safely
Do not test for the error code. =IFERROR(OFFSET(...),0) catches both engines'
versions; a check written specifically for #REF! catches only Excel's. Guarding the
bounds yourself is better still, because then neither engine errors:
=IF(ROW(anchor)+rows<1,"",OFFSET(anchor,rows,cols)) refuses the impossible move before
the function sees it.
The structural fix is to stop using OFFSET for dynamic ranges. INDEX
builds the same references without a volatile recalculation on every edit, and every
INDEX case in our corpus matched in LibreOffice 25.8.7.3 — so
=SUM(INDEX(A:A,start):INDEX(A:A,finish)) is both the faster and the more portable
pattern. When you do keep OFFSET, treat any formula whose row or column argument can go
negative as a review item during migration; that is precisely the set of formulas whose error
identity changes.
Honest limits
The Excel column is Microsoft's documented behaviour recorded in our test corpus, not a value we executed in Excel. Google Sheets is not yet run through our harness, so that column is left honest. The LibreOffice values are executed output from LibreOffice Calc 25.8.7.3 and were reproduced in our three earlier version runs.