SPB Git

Repository Public

numerical-precision.md 21.3 KB

# Numerical precision

This document states exactly how ORIX Sheet represents and computes numbers: the value types, IEEE-754 doubles in cells, the places where decimal arithmetic is used instead, date serial numbers, and the comparison and coercion rules. It ends with the list of behaviours that are deliberately or knowingly different from Microsoft Excel.

Results written "formula → result" were observed on 2026-11-21 by evaluating the formula with CalcEngine.evaluate in a throw-away executable linked against Packages/SheetEngine (debug build, Apple Silicon). Statements about Excel are limited to what the package documents of this repository record, or to behaviour documented by Microsoft; Excel itself was never run to validate ORIX Sheet (see reference-tests.md).

Related documents: ../architecture/formula-engine.md, financial-conventions.md, ../compatibility/excel.md, ../compatibility/formulas.md.

# Value types

A cell value is a Value (Packages/SheetCore/Sources/SheetCore/Model/Value.swift):

Case Content Notes
.blank nothing an empty cell; never the stored result of a formula (see below)
.number(Double) IEEE-754 binary64 dates and times are numbers interpreted by the number format
.text(String) Swift String text functions count UTF-16 code units, like Excel (FunctionsText/COMPATIBILITY.md)
.bool(Bool) TRUE / FALSE a distinct type, not the numbers 1 / 0
.error(CellError) one of ten error codes #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A, #SPILL!, #CALC!, and the ORIX extension #CIRC!
.array(ArrayValue) rows × cols of scalar values, row-major never nested; only exists during evaluation — a stored array is spilled into cells
.lambda(LambdaValue) a LAMBDA closure only during evaluation; a formula that returns one displays #CALC!

Source: Packages/SheetCore/Sources/SheetCore/Model/Value.swift.

There is no integer type, no decimal type and no date type in cells. Every number is a double.

# IEEE-754 doubles in cells

Cell arithmetic (+ - * / ^, unary minus, %) is performed directly on Double values by EvalContext.binary with no intermediate rounding, no extended precision and no decimal fix-up:

Formula Result
=0.1+0.2 displayed 0.3 (General shows 15 significant digits); the stored double is 0.30000000000000004
=0.1+0.2=0.3 FALSE
=0.5-0.4-0.1 -2.77555756156289E-17
=1*(0.5-0.4-0.1) -2.77555756156289E-17
=1E16+1-1E16 0
=SUM(1E16,1,-1E16) 1
=1/0 #DIV/0!
=1E308*10 #NUM!
=1E400 #NUM!
=0^0 #NUM!
=0^-1 #DIV/0!
=(-8)^(1/3) #NUM!

Source: probe run against CalcEngine, 2026-11-21.

Rules behind the table:

  • Non-finite results never reach a cell. Value.num(_:) maps infinity and NaN to #NUM!. Division by zero is tested explicitly and gives #DIV/0!.
  • Power uses Foundation.pow, with two explicit cases: 0^0 → #NUM! and 0^negative → #DIV/0!. A negative base with a fractional exponent yields NaN in pow and therefore #NUM!.
  • Negative zero can be produced (=-0) and is displayed as 0 (Value.generalString and GeneralFormat return "0" for any zero).
  • Integer arguments of functions are obtained with Value.int(): truncation toward zero, and #NUM! when the magnitude is 9.0e15 or more.

# Compensated summation

SUM and AVERAGE do not add naively. CompensatedSum (Engine/Builtins.swift) implements Neumaier's variant of Kahan summation: a running compensation term collects the low-order bits lost at each addition, choosing the branch by comparing magnitudes, and the result is sum + compensation. That is why =SUM(1E16,1,-1E16) is 1 while the chained operator expression =1E16+1-1E16 is 0 — operators are plain double arithmetic.

CompensatedSum is also used in the function libraries: SUMSQ, SUMPRODUCT, SUMX2PY2, SUMXMY2, SERIESSUM (FunctionsNumeric/Math.swift), the conditional sums and averages (FunctionsNumeric/Conditional.swift), descriptive statistics (Stats.swift, StatsBasic.swift, StatsPaired.swift), Regression.swift, DistributionsDiscrete.swift, and in FunctionsLookup/FinancialFunctions.swift.

Source: grep -rn CompensatedSum Packages/*/Sources, 2026-11-21.

Compensated summation makes the result independent of most cancellation effects, but it remains a double: it is not exact arithmetic. For references, values are visited in storage order (the order is documented as unspecified in EvalContext.forEachValue), which compensated summation makes harmless in practice but which means bit-identical results across differently built sheets are not guaranteed.

# Decimal-correct ROUND

ROUND, ROUNDUP, ROUNDDOWN and TRUNC do not round the binary value. They implement what Packages/FunctionsNumeric/COMPATIBILITY.md calls the 15-digit rule (Rounding.swift):

  1. Fast path. The number is scaled by an exact power of ten (10^0 … 10^22 are exact doubles). When the scaled magnitude is below 1e15 and its fractional part is farther than a relative 1e-10 from a rounding boundary (0.5 for half-away, 0 or 1 for up/down), binary noise cannot change the outcome and the result is computed directly.
  2. Slow path. Otherwise the number is printed with %.14e — its correctly rounded 15 significant decimal digits — and the rounding is performed on that digit string (half away from zero, up, or down), with carry propagation; the result is converted back with Double(String).
Formula Result Why
=ROUND(1.005,2) 1.01 the double is 1.00499999999999989…, its 15-digit reading is 1.00500000000000
=ROUND(2.5,0) 3 half away from zero
=ROUND(-2.5,0) -3 half away from zero, symmetric
=ROUND(2.4999999999999996,0) 3 the 15-digit reading is 2.50000000000000

Source: probe run 2026-11-21; method in Packages/FunctionsNumeric/Sources/FunctionsNumeric/Rounding.swift.

The last row is the price of the rule and is recorded as a known deviation: values that differ from a tie only beyond the 15th significant digit are treated as ties. CEILING, FLOOR, MROUND, EVEN and ODD use a related guard, snapInteger: a quotient within a relative 1e-14 of an integer is taken to be that integer before rounding, so CEILING(1.1,0.1) is 1.1 and not 1.2. The package validates the family against 4 082 cases computed with Python's decimal module at tolerance 0 (exact equality).

# Decimal-correct number formatting

Displaying a number is a separate code path (Packages/SheetFormat), built on the same idea. Sig.of (DecimalDigits.swift) extracts the 15 significant digits with %.14e and all subsequent rounding is done on decimal digits, half away from zero; digits beyond the 15th are zeros. Consequences recorded in Packages/SheetFormat/COMPATIBILITY.md: 0.285 formatted 0.00 gives 0.29, 1.005 gives 1.01, and 0.1+0.2 formatted with two decimals gives 0.30.

General keeps up to 15 significant digits, switches to scientific notation at 1E+15 and below 1E-9, and in a cell rounds decimals to fit the column before falling back to scientific notation and then to # fill. TEXT(x,"General") is limited to 11 characters.

There are two implementations of the General rendering and they agree on the 15-digit principle but are not the same code: Value.generalString in SheetCore (used when a number is coerced to text, as in =5&"") and GeneralFormat in SheetFormat (used for display). Observed coercions: =0.1+0.2&"" → 0.3, =1/3&"" → 0.333333333333333, =1E15&"" → 1E+15, =123456789012345678&"" → 1.23456789012346E+17.

Display never changes the stored value. There is no "precision as displayed" option: the double in the cell is what formulas read, whatever the format shows.

# Decimal arithmetic in amortization schedules

The loan tools of Packages/SheetAnalytics (not the worksheet functions PMT, IPMT…, which are double arithmetic) keep money in Foundation Decimal:

  • Money.decimal(_:) converts a double through its shortest round-trip decimal string (at most 17 significant digits), so 0.1 becomes exactly 0.1 rather than 0.1000000000000000055….
  • MoneyRounding rounds to a scale (default 2, cents) with a mode: halfUp (half away from zero, default), halfEven (banker's), down, up, implemented with NSDecimalRound.
  • Amortization.schedule rounds the level payment once (the payment itself comes from the double-precision TVM formula), computes each period's interest as round(balance × rate) in decimal arithmetic on the rounded balance, takes principal as payment − interest, and makes the final payment whatever clears the balance exactly. The schedule therefore always ends at 0.00 and the accumulated rounding residue is reported as finalPaymentAdjustment.
  • When a schedule is written to cells, each Decimal is converted back to the nearest double (Money.value).

Source: Packages/SheetAnalytics/Sources/FinanceModels/Core/Money.swift, Loans/Amortization.swift, Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md.

Note the two different decimal readings in the code base: ROUND and number formats read 15 significant digits; Money.decimal reads the shortest round-trip string (up to 17). Both turn 1.005 into a decimal tie that rounds to 1.01, but they can differ on doubles that need 16 or 17 digits to round-trip. The full conventions are in financial-conventions.md.

# Accuracy of library functions

The accuracy targets and methods of the statistical, special and engineering functions (Loader's saddle-point expansion, continued fractions, Brent's method for quantiles, LAPACK through Accelerate for matrix functions and regressions) are documented per function in Packages/FunctionsNumeric/COMPATIBILITY.md; the comparison tolerances (1e-12 relative for most scipy/mpmath references, looser where stated) are summarized in reference-tests.md.

# Date systems

Dates are serial day numbers with the time of day as the fractional part (Packages/SheetCore/Sources/SheetCore/Model/DateSerial.swift). Conversion to and from calendar dates uses exact integer arithmetic on the proleptic Gregorian calendar (the daysFromCivil / civilFromDays algorithms credited to Howard Hinnant in the source); no Foundation.Calendar is involved in serial ↔ date conversion.

System Epoch Leap-year bug Selected by
1900 (default) serial 1 = 1 January 1900 reproduced: serial 60 is the non-existent 29 February 1900 Workbook.date1904 == false
1904 serial 0 = 1 January 1904 none Workbook.date1904 == true

Source: Packages/SheetCore/Sources/SheetCore/Model/DateSerial.swift.

The 1900 system reproduces the Lotus 1-2-3 bug deliberately, for file compatibility:

Formula Result
=DATE(1900,2,29) 60
=DATE(1900,3,1) 61
=DAY(60) 29
=WEEKDAY(1) 1 (1 January 1900 is reported as a Sunday; it was a Monday)

Source: probe run 2026-11-21.

Implementation details:

  • DateSerial.serial adds the phantom day for every date from 1 March 1900 on; components returns (1900, 2, 29) for serial 60 and (1900, 1, 0) for serial 0 ("January 0, 1900").
  • Month and day arguments may overflow in both directions (DATE(2024,13,1), DATE(1900,3,0) = 60).
  • The time of day is rounded to the nearest millisecond (DateSerial.time), never rolling into the next day. HOUR/MINUTE/SECOND round to the nearest second first (FunctionsText/COMPATIBILITY.md).
  • The date functions accept serials 0 … 2 958 465 (31 December 9999) and return #NUM! outside (DateEnv.maxSerial). In formats, negative values and serials past 31 December 9999 render ######## (the 1904 system shows negative dates with a leading minus), per SheetFormat/COMPATIBILITY.md.
  • NOW() and TODAY() convert the system clock with the current time zone; serials themselves carry no time zone.
  • A double has about 15.95 significant decimal digits: a present-day serial (around 46 000) with a time fraction resolves to under a microsecond (one unit in the last place near 46 000 is about 7.3e-12 day), so the millisecond rounding above is a display rule, not a storage limit.

# Coercion rules

Conversions are methods of Value and are applied by operators and by every function argument declared as scalar:

Target blank number text boolean error
number (num()) 0 itself parsed if numeric text, else #VALUE! TRUE → 1, FALSE → 0 propagates
text (str()) "" General rendering, 15 significant digits itself TRUE / FALSE propagates
logical (truth()) FALSE ≠ 0 "TRUE" / "FALSE" (any case), else #VALUE! itself propagates

Source: Packages/SheetCore/Sources/SheetCore/Model/Value.swift.

Numeric text (Value.parseNumber) is accepted in this form: surrounding spaces, an optional sign, digits with an optional decimal point and exponent, a trailing % (divides by 100), a leading $, enclosing parentheses (negative), and comma thousands separators only when every group after the first has exactly three digits. Observed: ="3"+1 → 4, ="1,234"+1 → 1235, ="$5"+1 → 6, =" 5 "+0 → 5, ="5%"+0 → 0.05, ="(5)"+0 → -5, ="1e3"+0 → 1000, =--"3" → 3, =TRUE+1 → 2.

This parser is locale independent (period decimal, comma grouping, $) — it is the rule for text met inside formulas. Text typed into a cell goes through InputParser, which does use the locale's decimal and grouping separators, currency symbol and day/month order.

Date-like text is not coerced in arithmetic. =1+"1/2/2024" → #VALUE!. Date text is understood only by the functions that take dates (DATEVALUE, VALUE, date arguments of the FunctionsText functions), in month/day/year or ISO order regardless of locale; the financial functions reject dates given as text (FunctionsLookup/COMPATIBILITY.md, limit 1).

Aggregates are stricter inside ranges than on direct arguments (EvalContext.forEachNumber): in a reference or an array only numbers are used — text (even numeric text) and booleans are ignored; a direct argument is coerced. Observed: =SUM(1,"2",TRUE) → 4, =SUM(A1:A3,"x") → #VALUE!. Errors propagate from both.

# Comparison rules

Comparison operators use compareValues:

  1. Type order: numbers < text < booleans (FALSE < TRUE). Values of different types are never equal: ="abc"<5 → FALSE, =TRUE>"z" → TRUE, =1=TRUE → FALSE, =""=0 → FALSE.
  2. Blank compares as the neutral value of the other side: equal to 0, to "" and to FALSE. Observed with an empty cell Z99: =Z99=0, =Z99="" and =Z99=FALSE are all TRUE.
  3. Numbers are compared exactly as doubles — no tolerance, no rounding to 15 digits.
  4. Text is compared by TextOrder (Packages/SheetCore/Sources/SheetCore/Model/Value.swift): case-insensitive, with a fixed collation (the en locale, whatever the user's region) so that results do not depend on the Mac's settings. Accented letters sort next to their base letter: "e" < "é" < "f", and "Forfait Équipe" sorts before "Forfait Soutien". Equality stays strict apart from case: ="a"="A" → TRUE, ="É"="é" → TRUE, ="é"="e" → FALSE, ="ß"="SS" → FALSE. Two strings the collation ranks equal without being equal ignoring case are ordered by their lower-cased form, so the order is total and deterministic. Pure ASCII strings take a byte-wise fast path with the same result.
  5. Errors are not compared: an error operand makes the result that error, the left operand first.

Lookup functions and SORT/UNIQUE use the same TextOrder for text (FunctionsLookup/Matching.swift) inside their own documented type-ordering rules (Packages/FunctionsLookup/COMPATIBILITY.md), and so do the <, > criteria of the criteria functions (FunctionsNumeric/Criteria.swift); criteria functions (COUNTIF, SUMIF…) have their own matching rules (Packages/FunctionsNumeric/COMPATIBILITY.md).

# Floating-point edge cases

Case Behaviour
Overflow, NaN #NUM! (never stored as a number by operators or by functions that return through Value.num)
Underflow gradual, to subnormals and then 0, as IEEE-754 prescribes; no error
Large integers exact up to 2^53; beyond, adjacent integers are not representable (=1E16+1-1E16 → 0)
Display of large integers 15 significant digits: 123456789012345678 shows as 1.23456789012346E+17
Trigonometric functions of large arguments computed by libm with full range reduction: =SIN(2^30) → -0.617326415046042 (no #NUM! limit)
MOD with a huge quotient computed: =MOD(1E20,3) → 1 (no #NUM! limit)
Formula text a numeric literal is printed with its shortest round-trip representation, so parsing and printing a formula does not alter its constants
Materializing a reference more than 20 000 000 cells in one dense array → #CALC!

Source: probe run 2026-11-21; Value.swift, Evaluator.swift, Printer.swift; MOD and trigonometric limits per Packages/FunctionsNumeric/COMPATIBILITY.md.

# What is different from Excel

Collected here are the numeric behaviours that differ from Excel on purpose or knowingly. Each is either documented in a package COMPATIBILITY.md or follows from the code quoted above. Where the Excel side is only "as documented by Microsoft" and was not verified, this is said.

Topic ORIX Sheet Excel (as recorded in the repository or documented) Status
Circular reference error value #CIRC!, no iteration warning and 0; optional iterative calculation deliberate (Value.swift)
Near-zero results of + and - plain binary result (=0.5-0.4-0.1 → -2.78E-17; =0.1+0.2=0.3 → FALSE) Microsoft documents that Excel may adjust an addition or subtraction result that is very close to zero not implemented; not verified against Excel
Summation Neumaier-compensated in SUM, AVERAGE and the listed aggregates not documented in the repository deliberate; results can be more accurate than a naive sum, and so differ in the last digits
ROUND family near ties 15-digit rule applied uniformly (ROUND(2.4999999999999996,0) = 3) matches every documented tie; behaviour beyond the 15th digit undocumented recorded in FunctionsNumeric/COMPATIBILITY.md
MOD no limit on the quotient #NUM! for very large quotients recorded, deliberate
SIN, COS, TAN… any finite argument #NUM! above 2^27 recorded, deliberate
Bessel functions accurate to about 1e-11 about 7 digits (per the help page example) recorded; results differ beyond the 7th digit
Iterative financial solvers (RATE, IRR, XIRR, YIELD) converge further and find roots where Excel reports #NUM! stops at 1e-7 / 20 Newton steps recorded in FunctionsLookup/COMPATIBILITY.md
Date-like text in arithmetic #VALUE! coerced to a serial number follows from Value.parseNumber; not verified against Excel
Numeric text coercion locale independent (. decimal, , grouping, $) uses the system locale follows from Value.parseNumber
Text comparison case-insensitive; fixed en collation for ordering (accents next to the base letter), strict equality apart from case system collation TextOrder in SheetCore/Model/Value.swift; a system whose collation differs from en can order some strings differently in Excel
Blank formula result stored and displayed as 0 displayed as 0 same as Excel (comment in CalcEngine.store)
1900 leap-year bug reproduced present same as Excel, deliberate
Precision as displayed not available optional workbook setting not implemented
Display of *x fill and _x spacing in formats reported but not expanded / one ordinary space proportional recorded in SheetFormat/COMPATIBILITY.md

Source: Packages/FunctionsNumeric/COMPATIBILITY.md, Packages/FunctionsLookup/COMPATIBILITY.md, Packages/FunctionsText/COMPATIBILITY.md, Packages/SheetFormat/COMPATIBILITY.md, Packages/SheetCore/Sources/SheetCore/Model/Value.swift, Engine/CalcEngine.swift; probe run 2026-11-21.

The per-function deviations (criteria matching, percentile ranks, regression with collinear columns, date parsing, regular-expression dialect, coupon schedules…) are listed function by function in ../compatibility/formulas.md.

# Limits of these statements

  • The observations above were made on one machine (Apple Silicon, macOS 27.0) in a debug build. The arithmetic involved is IEEE-754 and libm; no claim is made about bit-identical results on Intel Macs, where libm implementations of transcendental functions may differ in the last unit.
  • "Matches Excel" in this repository always means "matches Excel's documentation, an Excel-computed public data set, or LibreOffice where it agrees with them". No test drives Excel.
  • Whether files written by ORIX Sheet preserve every double bit for bit is a property of each file format and is covered in ../compatibility/formats.md and ../architecture/persistence.md, not here.
Files main
Menu

Theme