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!and0^negative→#DIV/0!. A negative base with a fractional exponent yields NaN inpowand therefore#NUM!. - Negative zero can be produced (
=-0) and is displayed as0(Value.generalStringandGeneralFormatreturn "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):
- 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.
- 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 withDouble(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….MoneyRoundingrounds to a scale (default 2, cents) with a mode:halfUp(half away from zero, default),halfEven(banker's),down,up, implemented withNSDecimalRound.Amortization.schedulerounds the level payment once (the payment itself comes from the double-precision TVM formula), computes each period's interest asround(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 asfinalPaymentAdjustment.- When a schedule is written to cells, each
Decimalis 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.serialadds the phantom day for every date from 1 March 1900 on;componentsreturns (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/SECONDround 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), perSheetFormat/COMPATIBILITY.md. NOW()andTODAY()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:
- 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. - Blank compares as the neutral value of the other side: equal to 0, to
""and to FALSE. Observed with an empty cellZ99:=Z99=0,=Z99=""and=Z99=FALSEare all TRUE. - Numbers are compared exactly as doubles — no tolerance, no rounding to 15 digits.
- Text is compared by
TextOrder(Packages/SheetCore/Sources/SheetCore/Model/Value.swift): case-insensitive, with a fixed collation (theenlocale, 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. - 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.