Repository Public
1# Numerical precision23This document states exactly how ORIX Sheet represents and computes numbers: the value types, IEEE-7544doubles in cells, the places where decimal arithmetic is used instead, date serial numbers, and the5comparison and coercion rules. It ends with the list of behaviours that are deliberately or knowingly6different from Microsoft Excel.78Results written "`formula` → result" were observed on 2026-11-21 by evaluating the formula with9`CalcEngine.evaluate` in a throw-away executable linked against `Packages/SheetEngine` (debug build, Apple10Silicon). Statements about Excel are limited to what the package documents of this repository record, or to11behaviour documented by Microsoft; **Excel itself was never run** to validate ORIX Sheet (see12[reference-tests.md](reference-tests.md)).1314Related documents: [../architecture/formula-engine.md](../architecture/formula-engine.md),15[financial-conventions.md](financial-conventions.md), [../compatibility/excel.md](../compatibility/excel.md),16[../compatibility/formulas.md](../compatibility/formulas.md).1718## Value types1920A cell value is a `Value` (`Packages/SheetCore/Sources/SheetCore/Model/Value.swift`):2122| Case | Content | Notes |23|---|---|---|24| `.blank` | nothing | an empty cell; never the stored result of a formula (see below) |25| `.number(Double)` | IEEE-754 binary64 | dates and times are numbers interpreted by the number format |26| `.text(String)` | Swift `String` | text functions count UTF-16 code units, like Excel (`FunctionsText/COMPATIBILITY.md`) |27| `.bool(Bool)` | TRUE / FALSE | a distinct type, not the numbers 1 / 0 |28| `.error(CellError)` | one of ten error codes | `#NULL!`, `#DIV/0!`, `#VALUE!`, `#REF!`, `#NAME?`, `#NUM!`, `#N/A`, `#SPILL!`, `#CALC!`, and the ORIX extension `#CIRC!` |29| `.array(ArrayValue)` | rows × cols of scalar values, row-major | never nested; only exists during evaluation — a stored array is spilled into cells |30| `.lambda(LambdaValue)` | a `LAMBDA` closure | only during evaluation; a formula that returns one displays `#CALC!` |3132Source: `Packages/SheetCore/Sources/SheetCore/Model/Value.swift`.3334There is **no integer type, no decimal type and no date type** in cells. Every number is a double.3536## IEEE-754 doubles in cells3738Cell arithmetic (`+ - * / ^`, unary minus, `%`) is performed directly on `Double` values by39`EvalContext.binary` with no intermediate rounding, no extended precision and no decimal fix-up:4041| Formula | Result |42|---|---|43| `=0.1+0.2` | displayed `0.3` (General shows 15 significant digits); the stored double is 0.30000000000000004 |44| `=0.1+0.2=0.3` | FALSE |45| `=0.5-0.4-0.1` | -2.77555756156289E-17 |46| `=1*(0.5-0.4-0.1)` | -2.77555756156289E-17 |47| `=1E16+1-1E16` | 0 |48| `=SUM(1E16,1,-1E16)` | 1 |49| `=1/0` | `#DIV/0!` |50| `=1E308*10` | `#NUM!` |51| `=1E400` | `#NUM!` |52| `=0^0` | `#NUM!` |53| `=0^-1` | `#DIV/0!` |54| `=(-8)^(1/3)` | `#NUM!` |5556Source: probe run against `CalcEngine`, 2026-11-21.5758Rules behind the table:5960- **Non-finite results never reach a cell.** `Value.num(_:)` maps infinity and NaN to `#NUM!`. Division by61 zero is tested explicitly and gives `#DIV/0!`.62- **Power** uses `Foundation.pow`, with two explicit cases: `0^0` → `#NUM!` and `0^negative` → `#DIV/0!`. A63 negative base with a fractional exponent yields NaN in `pow` and therefore `#NUM!`.64- **Negative zero** can be produced (`=-0`) and is displayed as `0` (`Value.generalString` and65 `GeneralFormat` return "0" for any zero).66- **Integer arguments** of functions are obtained with `Value.int()`: truncation toward zero, and `#NUM!`67 when the magnitude is 9.0e15 or more.6869### Compensated summation7071`SUM` and `AVERAGE` do not add naively. `CompensatedSum` (`Engine/Builtins.swift`) implements Neumaier's72variant of Kahan summation: a running compensation term collects the low-order bits lost at each addition,73choosing the branch by comparing magnitudes, and the result is `sum + compensation`. That is why74`=SUM(1E16,1,-1E16)` is 1 while the chained operator expression `=1E16+1-1E16` is 0 — operators are plain75double arithmetic.7677`CompensatedSum` is also used in the function libraries: `SUMSQ`, `SUMPRODUCT`, `SUMX2PY2`, `SUMXMY2`,78`SERIESSUM` (`FunctionsNumeric/Math.swift`), the conditional sums and averages79(`FunctionsNumeric/Conditional.swift`), descriptive statistics (`Stats.swift`, `StatsBasic.swift`,80`StatsPaired.swift`), `Regression.swift`, `DistributionsDiscrete.swift`, and in81`FunctionsLookup/FinancialFunctions.swift`.8283Source: `grep -rn CompensatedSum Packages/*/Sources`, 2026-11-21.8485Compensated summation makes the result independent of most cancellation effects, but it remains a double:86it is not exact arithmetic. For references, values are visited in storage order (the order is documented as87unspecified in `EvalContext.forEachValue`), which compensated summation makes harmless in practice but which88means bit-identical results across differently built sheets are not guaranteed.8990### Decimal-correct ROUND9192`ROUND`, `ROUNDUP`, `ROUNDDOWN` and `TRUNC` do not round the binary value. They implement what93`Packages/FunctionsNumeric/COMPATIBILITY.md` calls the 15-digit rule (`Rounding.swift`):94951. **Fast path.** The number is scaled by an exact power of ten (10^0 … 10^22 are exact doubles). When the96 scaled magnitude is below 1e15 and its fractional part is farther than a relative 1e-10 from a rounding97 boundary (0.5 for half-away, 0 or 1 for up/down), binary noise cannot change the outcome and the result98 is computed directly.992. **Slow path.** Otherwise the number is printed with `%.14e` — its correctly rounded **15 significant100 decimal digits** — and the rounding is performed on that digit string (half away from zero, up, or101 down), with carry propagation; the result is converted back with `Double(String)`.102103| Formula | Result | Why |104|---|---|---|105| `=ROUND(1.005,2)` | 1.01 | the double is 1.00499999999999989…, its 15-digit reading is 1.00500000000000 |106| `=ROUND(2.5,0)` | 3 | half away from zero |107| `=ROUND(-2.5,0)` | -3 | half away from zero, symmetric |108| `=ROUND(2.4999999999999996,0)` | 3 | the 15-digit reading is 2.50000000000000 |109110Source: probe run 2026-11-21; method in `Packages/FunctionsNumeric/Sources/FunctionsNumeric/Rounding.swift`.111112The last row is the price of the rule and is recorded as a known deviation: values that differ from a tie113only beyond the 15th significant digit are treated as ties. `CEILING`, `FLOOR`, `MROUND`, `EVEN` and `ODD`114use a related guard, `snapInteger`: a quotient within a relative 1e-14 of an integer is taken to be that115integer before rounding, so `CEILING(1.1,0.1)` is 1.1 and not 1.2. The package validates the family against1164 082 cases computed with Python's `decimal` module at tolerance 0 (exact equality).117118### Decimal-correct number formatting119120Displaying a number is a separate code path (`Packages/SheetFormat`), built on the same idea. `Sig.of`121(`DecimalDigits.swift`) extracts the 15 significant digits with `%.14e` and **all subsequent rounding is122done on decimal digits**, half away from zero; digits beyond the 15th are zeros. Consequences recorded in123`Packages/SheetFormat/COMPATIBILITY.md`: 0.285 formatted `0.00` gives `0.29`, 1.005 gives `1.01`, and1240.1+0.2 formatted with two decimals gives `0.30`.125126`General` keeps up to 15 significant digits, switches to scientific notation at 1E+15 and below 1E-9, and in127a cell rounds decimals to fit the column before falling back to scientific notation and then to `#` fill.128`TEXT(x,"General")` is limited to 11 characters.129130There are two implementations of the General rendering and they agree on the 15-digit principle but are not131the same code: `Value.generalString` in SheetCore (used when a number is coerced to text, as in `=5&""`) and132`GeneralFormat` in SheetFormat (used for display). Observed coercions: `=0.1+0.2&""` → `0.3`,133`=1/3&""` → `0.333333333333333`, `=1E15&""` → `1E+15`, `=123456789012345678&""` → `1.23456789012346E+17`.134135**Display never changes the stored value.** There is no "precision as displayed" option: the double in the136cell is what formulas read, whatever the format shows.137138### Decimal arithmetic in amortization schedules139140The loan tools of `Packages/SheetAnalytics` (not the worksheet functions `PMT`, `IPMT`…, which are double141arithmetic) keep money in Foundation `Decimal`:142143- `Money.decimal(_:)` converts a double through its **shortest round-trip decimal string** (at most 17144 significant digits), so 0.1 becomes exactly 0.1 rather than 0.1000000000000000055….145- `MoneyRounding` rounds to a scale (default 2, cents) with a mode: `halfUp` (half away from zero, default),146 `halfEven` (banker's), `down`, `up`, implemented with `NSDecimalRound`.147- `Amortization.schedule` rounds the level payment once (the payment itself comes from the double-precision148 TVM formula), computes each period's interest as `round(balance × rate)` in decimal arithmetic on the149 rounded balance, takes principal as payment − interest, and makes the final payment whatever clears the150 balance exactly. The schedule therefore always ends at 0.00 and the accumulated rounding residue is151 reported as `finalPaymentAdjustment`.152- When a schedule is written to cells, each `Decimal` is converted back to the nearest double153 (`Money.value`).154155Source: `Packages/SheetAnalytics/Sources/FinanceModels/Core/Money.swift`, `Loans/Amortization.swift`, `Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md`.156157Note the two different decimal readings in the code base: `ROUND` and number formats read **15** significant158digits; `Money.decimal` reads the **shortest round-trip** string (up to 17). Both turn 1.005 into a decimal159tie that rounds to 1.01, but they can differ on doubles that need 16 or 17 digits to round-trip. The full160conventions are in [financial-conventions.md](financial-conventions.md).161162### Accuracy of library functions163164The accuracy targets and methods of the statistical, special and engineering functions (Loader's165saddle-point expansion, continued fractions, Brent's method for quantiles, LAPACK through Accelerate for166matrix functions and regressions) are documented per function in167`Packages/FunctionsNumeric/COMPATIBILITY.md`; the comparison tolerances (1e-12 relative for most168scipy/mpmath references, looser where stated) are summarized in [reference-tests.md](reference-tests.md).169170## Date systems171172Dates are serial day numbers with the time of day as the fractional part173(`Packages/SheetCore/Sources/SheetCore/Model/DateSerial.swift`). Conversion to and from calendar dates uses174exact integer arithmetic on the proleptic Gregorian calendar (the `daysFromCivil` / `civilFromDays`175algorithms credited to Howard Hinnant in the source); no `Foundation.Calendar` is involved in serial ↔ date176conversion.177178| System | Epoch | Leap-year bug | Selected by |179|---|---|---|---|180| 1900 (default) | serial 1 = 1 January 1900 | **reproduced**: serial 60 is the non-existent 29 February 1900 | `Workbook.date1904 == false` |181| 1904 | serial 0 = 1 January 1904 | none | `Workbook.date1904 == true` |182183Source: `Packages/SheetCore/Sources/SheetCore/Model/DateSerial.swift`.184185The 1900 system reproduces the Lotus 1-2-3 bug deliberately, for file compatibility:186187| Formula | Result |188|---|---|189| `=DATE(1900,2,29)` | 60 |190| `=DATE(1900,3,1)` | 61 |191| `=DAY(60)` | 29 |192| `=WEEKDAY(1)` | 1 (1 January 1900 is reported as a Sunday; it was a Monday) |193194Source: probe run 2026-11-21.195196Implementation details:197198- `DateSerial.serial` adds the phantom day for every date from 1 March 1900 on; `components` returns199 (1900, 2, 29) for serial 60 and (1900, 1, 0) for serial 0 ("January 0, 1900").200- Month and day arguments may overflow in both directions (`DATE(2024,13,1)`, `DATE(1900,3,0)` = 60).201- The time of day is rounded to the nearest **millisecond** (`DateSerial.time`), never rolling into the next202 day. `HOUR`/`MINUTE`/`SECOND` round to the nearest second first (`FunctionsText/COMPATIBILITY.md`).203- The date functions accept serials 0 … 2 958 465 (31 December 9999) and return `#NUM!` outside204 (`DateEnv.maxSerial`). In formats, negative values and serials past 31 December 9999 render `########`205 (the 1904 system shows negative dates with a leading minus), per `SheetFormat/COMPATIBILITY.md`.206- `NOW()` and `TODAY()` convert the system clock with the current time zone; serials themselves carry no207 time zone.208- A double has about 15.95 significant decimal digits: a present-day serial (around 46 000) with a time209 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 a210 storage limit.211212## Coercion rules213214Conversions are methods of `Value` and are applied by operators and by every function argument declared as215scalar:216217| Target | blank | number | text | boolean | error |218|---|---|---|---|---|---|219| number (`num()`) | 0 | itself | parsed if numeric text, else `#VALUE!` | TRUE → 1, FALSE → 0 | propagates |220| text (`str()`) | `""` | General rendering, 15 significant digits | itself | `TRUE` / `FALSE` | propagates |221| logical (`truth()`) | FALSE | ≠ 0 | `"TRUE"` / `"FALSE"` (any case), else `#VALUE!` | itself | propagates |222223Source: `Packages/SheetCore/Sources/SheetCore/Model/Value.swift`.224225**Numeric text** (`Value.parseNumber`) is accepted in this form: surrounding spaces, an optional sign,226digits with an optional decimal point and exponent, a trailing `%` (divides by 100), a leading `$`,227enclosing parentheses (negative), and comma thousands separators only when every group after the first has228exactly three digits. Observed: `="3"+1` → 4, `="1,234"+1` → 1235, `="$5"+1` → 6, `=" 5 "+0` → 5,229`="5%"+0` → 0.05, `="(5)"+0` → -5, `="1e3"+0` → 1000, `=--"3"` → 3, `=TRUE+1` → 2.230231This parser is **locale independent** (period decimal, comma grouping, `$`) — it is the rule for text met232inside formulas. Text typed into a cell goes through `InputParser`, which does use the locale's decimal and233grouping separators, currency symbol and day/month order.234235**Date-like text is not coerced in arithmetic.** `=1+"1/2/2024"` → `#VALUE!`. Date text is understood only236by the functions that take dates (`DATEVALUE`, `VALUE`, date arguments of the FunctionsText functions), in237month/day/year or ISO order regardless of locale; the financial functions reject dates given as text238(`FunctionsLookup/COMPATIBILITY.md`, limit 1).239240**Aggregates are stricter inside ranges than on direct arguments** (`EvalContext.forEachNumber`): in a241reference or an array only numbers are used — text (even numeric text) and booleans are ignored; a direct242argument is coerced. Observed: `=SUM(1,"2",TRUE)` → 4, `=SUM(A1:A3,"x")` → `#VALUE!`. Errors propagate from243both.244245## Comparison rules246247Comparison operators use `compareValues`:2482491. **Type order**: numbers < text < booleans (FALSE < TRUE). Values of different types are never equal:250 `="abc"<5` → FALSE, `=TRUE>"z"` → TRUE, `=1=TRUE` → FALSE, `=""=0` → FALSE.2512. **Blank** compares as the neutral value of the other side: equal to 0, to `""` and to FALSE. Observed with252 an empty cell `Z99`: `=Z99=0`, `=Z99=""` and `=Z99=FALSE` are all TRUE.2533. **Numbers** are compared exactly as doubles — no tolerance, no rounding to 15 digits.2544. **Text** is compared by `TextOrder` (`Packages/SheetCore/Sources/SheetCore/Model/Value.swift`):255 case-insensitive, with a **fixed collation** (the `en` locale, whatever the user's region) so that256 results do not depend on the Mac's settings. Accented letters sort next to their base letter:257 `"e"` < `"é"` < `"f"`, and `"Forfait Équipe"` sorts before `"Forfait Soutien"`. Equality stays strict258 apart from case: `="a"="A"` → TRUE, `="É"="é"` → TRUE, `="é"="e"` → FALSE, `="ß"="SS"` → FALSE. Two259 strings the collation ranks equal without being equal ignoring case are ordered by their lower-cased260 form, so the order is total and deterministic. Pure ASCII strings take a byte-wise fast path with261 the same result.2625. **Errors** are not compared: an error operand makes the result that error, the left operand first.263264Lookup functions and `SORT`/`UNIQUE` use the same `TextOrder` for text (`FunctionsLookup/Matching.swift`)265inside their own documented type-ordering rules (`Packages/FunctionsLookup/COMPATIBILITY.md`), and so266do the `<`, `>` criteria of the criteria functions (`FunctionsNumeric/Criteria.swift`); criteria functions (`COUNTIF`, `SUMIF`…) have their own267matching rules (`Packages/FunctionsNumeric/COMPATIBILITY.md`).268269## Floating-point edge cases270271| Case | Behaviour |272|---|---|273| Overflow, NaN | `#NUM!` (never stored as a number by operators or by functions that return through `Value.num`) |274| Underflow | gradual, to subnormals and then 0, as IEEE-754 prescribes; no error |275| Large integers | exact up to 2^53; beyond, adjacent integers are not representable (`=1E16+1-1E16` → 0) |276| Display of large integers | 15 significant digits: 123456789012345678 shows as 1.23456789012346E+17 |277| Trigonometric functions of large arguments | computed by libm with full range reduction: `=SIN(2^30)` → -0.617326415046042 (no `#NUM!` limit) |278| `MOD` with a huge quotient | computed: `=MOD(1E20,3)` → 1 (no `#NUM!` limit) |279| Formula text | a numeric literal is printed with its shortest round-trip representation, so parsing and printing a formula does not alter its constants |280| Materializing a reference | more than 20 000 000 cells in one dense array → `#CALC!` |281282Source: probe run 2026-11-21; `Value.swift`, `Evaluator.swift`, `Printer.swift`; MOD and trigonometric limits per `Packages/FunctionsNumeric/COMPATIBILITY.md`.283284## What is different from Excel285286Collected here are the numeric behaviours that differ from Excel **on purpose or knowingly**. Each is287either documented in a package `COMPATIBILITY.md` or follows from the code quoted above. Where the Excel288side is only "as documented by Microsoft" and was not verified, this is said.289290| Topic | ORIX Sheet | Excel (as recorded in the repository or documented) | Status |291|---|---|---|---|292| Circular reference | error value `#CIRC!`, no iteration | warning and 0; optional iterative calculation | deliberate (`Value.swift`) |293| 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 |294| 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 |295| `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` |296| `MOD` | no limit on the quotient | `#NUM!` for very large quotients | recorded, deliberate |297| `SIN`, `COS`, `TAN`… | any finite argument | `#NUM!` above 2^27 | recorded, deliberate |298| Bessel functions | accurate to about 1e-11 | about 7 digits (per the help page example) | recorded; results differ beyond the 7th digit |299| 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` |300| Date-like text in arithmetic | `#VALUE!` | coerced to a serial number | follows from `Value.parseNumber`; not verified against Excel |301| Numeric text coercion | locale independent (`.` decimal, `,` grouping, `$`) | uses the system locale | follows from `Value.parseNumber` |302| 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 |303| Blank formula result | stored and displayed as 0 | displayed as 0 | same as Excel (comment in `CalcEngine.store`) |304| 1900 leap-year bug | reproduced | present | same as Excel, deliberate |305| Precision as displayed | not available | optional workbook setting | not implemented |306| Display of `*x` fill and `_x` spacing in formats | reported but not expanded / one ordinary space | proportional | recorded in `SheetFormat/COMPATIBILITY.md` |307308Source: `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.309310The per-function deviations (criteria matching, percentile ranks, regression with collinear columns, date311parsing, regular-expression dialect, coupon schedules…) are listed function by function in312[../compatibility/formulas.md](../compatibility/formulas.md).313314## Limits of these statements315316- The observations above were made on one machine (Apple Silicon, macOS 27.0) in a debug build. The317 arithmetic involved is IEEE-754 and libm; no claim is made about bit-identical results on Intel Macs,318 where libm implementations of transcendental functions may differ in the last unit.319- "Matches Excel" in this repository always means "matches Excel's documentation, an Excel-computed public320 data set, or LibreOffice where it agrees with them". No test drives Excel.321- Whether files written by ORIX Sheet preserve every double bit for bit is a property of each file format322 and is covered in [../compatibility/formats.md](../compatibility/formats.md) and323 [../architecture/persistence.md](../architecture/persistence.md), not here.324