Coercion

Grid is loosely typed at the value level, like a spreadsheet: a formula may receive a number where it expected text, a blank where it expected a number, or a boolean in an arithmetic expression. Coercion is the set of rules that decide what happens.

The rules below describe Grid's value conversions.

For the kinds and tags these rules operate on, see reference.md. For how errors flow (they do not coerce — they propagate), see errors.md.

1. Where Coercion Happens

There are four places a value may be coerced:

1. Operators — arithmetic, comparison, concatenation, logical.
2. Function arguments — a function that expects a number coerces what it can and errors on what it can't.
3. Type-tagged assignments — A1 is currency = "100" coerces the string to a number before the tag check.
4. Rule-action read-modify-write — add/sub/mul/div coerce to number; concat/prepend coerce to text.

Coercion never mutates a stored value: it produces a converted value for the operation at hand. The binding keeps whatever kind its formula produced.

2. Coercion To Number

Used by arithmetic (+ - * / // ^ %% MOD), numeric comparison, and any function that wants a number.

"5" + 3        # 8     (numeric string coerces)
TRUE + 1       # 2     (TRUE = 1)
5 + BLANK      # 5     (blank = 0)
"abc" + 1      # #VALUE!  (non-numeric string)

Blank as zero is what makes self-referential accumulators settle: E1 = E1 + 1 resolves to 1 on first fire because E1 is blank (→ 0) before it exists.

3. Coercion To Boolean

Used by IF, AND/OR/XOR/NOT, conditions in WHEN, and the THEN … ELSE guard.

IF(0, "y", "n")        # "n"   (0 is falsy)
IF("", "y", "n")       # "n"   (empty string is falsy)
IF("text", "y", "n")   # "y"   (non-empty string is truthy)
IF(BLANK, "y", "n")    # "n"

4. Coercion To Text

Used by concatenation (&), string functions, and interpolation.

"foo" & "bar"          # "foobar"
"count: " & 42         # "count: 42"     (number coerces to text)
`net={A1}`             # interpolation coerces A1 to text

For controlled formatting (currency, thousands separators, fixed decimals), use TEXT(value, "pattern") or an interpolation format spec (`{A1:"$#,##0.00"}`) rather than relying on default text coercion.

5. Comparison Across Kinds

Grid operator equality (=, <>/!=) is type-sensitive and case-sensitive: it does not coerce text to a number, and text values compare by exact code points. The type boundary matches Excel operator equality—a number is not equal to numeric-looking text—but Excel's operator text equality is case-insensitive. Use EXACT when spelling the shared Grid/Excel case-sensitive operation explicitly. Coerce explicitly when numeric text should compare as a number. Exact numeric values remain interoperable with compatible ordinary numbers because both are numeric representations.

5 = 5                    # TRUE
"5" = 5                  # FALSE — string and number are different kinds
"Grid" = "grid"          # FALSE — Grid operator equality is case-sensitive
EXACT("Grid", "grid")    # FALSE — explicit and portable case-sensitive test
VALUE("5") = 5           # TRUE  — conversion is explicit
BIGINT(5) = 5            # TRUE  — exact and ordinary numeric representations

Ordering (<, <=, >, >=) compares two text operands lexically, even when both strings look numeric. For different kinds, loose mode coerces both sides to number where possible. If they cannot be compared numerically, Grid uses Excel's cross-kind ordering:

number  <  text  <  boolean

So any text sorts greater than any number, and any boolean greater than any text:

"10" < "2"      # TRUE   (same-kind lexical comparison)
"5" < 6         # TRUE   (different kinds; loose numeric coercion)
BIGINT(2) < "1" # FALSE  (numeric text is promoted into the exact domain)
"a" > 1         # TRUE   (text outranks number)

Exact ordering never widens a noninteger binary float into an exact value: BIGINT(2) < 1.5 and BIGINT(2) < "1.5" return #VALUE!. Under coercion warn, a successful numeric-text promotion retains the loose result and emits one warning; under coercion strict, it returns #TYPE!.

For case-insensitive text comparison, use ILIKE or fold case with LOWER/UPPER first.

6. Operator Coercion Summary

6.1 Strict Mode Rejects Silent Coercion

The table above describes loose, the default. A model headed with strict (see reference.md) turns a lossy coercion in an arithmetic or comparison operand into a TYPE_ERROR instead of converting silently:

strict
A2 = A1 + 1      # #TYPE! when A1 is blank or "5"
A2 = 40 + 2      # 42 — no coercion happened

Only the silent conversion is rejected. A genuine number flows through untouched, so strict never changes the result of arithmetic that had no coercion to begin with. strict except coercion keeps this axis loose while still tightening dimensions. To set this axis by itself — including the warn middle state the umbrella cannot spell — use the per-axis header below.

6.2 The Per-Axis coercion Header

coercion strict|warn|loose|off sets the coercion axis alone, without touching dimensions (mirror of the dimensions header for units; see reference.md):

Directives combine last-wins per axis, so a coercion header after the umbrella overrides just this axis. Rejecting on this axis alone:

coercion strict
A1 = BLANK
A2 = A1 + 1      # #TYPE! — blank → 0 is rejected on this axis alone

And keeping results loose while surfacing the conversions:

coercion warn
A1 = "5"
A2 = A1 + 3      # 8 — the lossy conversion is flagged, not rejected

coercion strict enforcement matches the umbrella's coercion axis today (scalar arithmetic and comparison operands). warn records the mode with the model and its diagnostics surface incrementally — not every lossy site emits its warning yet.

7. Type-Tag Coercion On Assignment

A type tag declares a semantic meaning over a kind. Under strict, on assignment the produced value is checked for kind compatibility with the tag's representation root, after the same value coercions above:

strict
A1 is currency = 100          # OK   — number is currency-compatible
A1 is currency = "100"        # OK   — string coerces to a number first
A1 is currency = "hello"      # #TYPE!  — not coercible to a number
A1 is percentage = 21pct      # OK   — 0.21, percentage-tagged

The check is part of the coercion axis — loose (the default) applies the tag as an overlay without checking, and strict except coercion also skips it. Either way, tags do not change arithmetic: a currency-tagged 1000 is still the number 1000 in A1 * 1.1. See reference.md and assignments.md.

7.1 Unit Conversion

INTO / TYPE_TAG overlays a semantic tag and participates in dimensional checking, but it does not rescale a number. Use CONVERT(value, from, to) for numeric unit conversion:

CONVERT(3, "m", "ft")       # 9.842519685...
CONVERT(10, "m/s", "km/h")  # 36
CONVERT(0, "C", "K")        # 273.15
CONVERT(10, "delta_C", "delta_F")  # 18

The conversion catalog understands ISO currency unit tags, SI/derived units, rational exponents, absolute temperature units (C, F, K), and explicit temperature delta units (delta_C, delta_F, delta_K). Absolute temperatures convert with offsets; deltas convert by scale only. Converting between absolute and delta temperature units returns #VALUE!.

Model-scoped custom units declared with unit <name> = ... participate in the same catalog for compiled models, as long as they are scale-only aliases over linear units.

Currency conversion never uses ambient FX. CONVERT(10, "USD", "EUR") returns #VALUE!; currency conversion must be explicit with FX_RATE or with a model-owned rate value declared by fx_rate <target> = BASE/QUOTE and populated from model data, a table, or an explicit connector input.

7.2 Unit-scale checking

Dimensional compatibility does not establish scale compatibility. Numeric unit tags remain overlays: 1km + 500m computes the raw magnitude 501. The compiler now diagnoses this with GRID_UNIT_SCALE_MISMATCH, identifies both unit representations, and recommends an explicit conversion. It does not attach an inferred physical unit to a result whose representation is conflicting or unknown. Proven homogeneous results retain their actual unit, so 1km + 2km has magnitude 3 with an inferred kilometer label.

strict
unit_scales strict
 
# Convert the boundary value into the working unit before arithmetic.
output Distance is unit:m = CONVERT(1, "km", "m") + 500m

Without a directive, the compiler warns about proven scale conflicts and preserves numeric compatibility, including in existing strict models. Explicit unit_scales warn also reports unverified uses in the covered audit. unit_scales strict makes known conflicts and reported unverified scale uses compile errors. unit_scales off skips this audit and retains legacy display inference. Last directive wins for this axis; strict, loose, and dimensions do not change it. Use both strict and unit_scales strict when dimension/coercion enforcement and scale enforcement are intended. This source declaration is preserved with the model; it is not a deployment profile or an automatic-conversion mode.

The analysis runs before constant folding and follows direct cell/named references, tagged literals, explicit conversion, basic arithmetic, powers, comparisons, homogeneous arrays and selected numeric functions. It checks retagging and the stated source unit of CONVERT, preventing an accidental double conversion. Equal-dimension ratios with conflicting scales must be normalized explicitly before cancellation. Temperature scale/offset and custom unit definitions use the shared unit catalog. Existing dimension and absolute/delta-temperature contracts continue to apply. Currency conversion continues to require explicit rates.

This is a static audit of covered expressions, not a proof of every physical assumption or runtime value. Reactive values, dynamic unit names and unsupported unit-bearing function contracts can produce GRID_UNIT_SCALE_UNVERIFIED. Unannotated numbers still require a declared working-unit convention. Input validation remains the existing write contract; the scale directive does not add a new per-write or per-cell evaluator. Unknown inferred unit labels are omitted rather than presented as established units. Use explicit, reviewed boundary conversions and tags, and retain the model's engineering assumptions.

8. Function Argument Coercion

Most built-ins coerce each argument to the kind they need, using the rules in §2–§4, and aggregate functions are lenient about non-numeric cells in a range:

SUM(1, "2", TRUE, BLANK)   # 4    (numeric strings, TRUE=1, blank=0)
SUM([1, "x", 3])           # 4    (range aggregation skips non-numeric)
ROUND("3.14159", 2)        # 3.14 (numeric string coerces)

A scalar function argument that cannot be coerced returns #VALUE!, while range aggregations (SUM, AVERAGE, COUNT, …) generally skip cells that aren't numeric rather than erroring. Use AGGREGATE with an error-ignoring mode when a range may contain error values:

AGGREGATE(9, 6, B1:B10)    # SUM, ignoring errors

See functions.md for each function's contract.

9. Errors Do Not Coerce

Error values are never coerced into a "normal" value by an operator or function — they propagate. The first error in a positional argument list wins:

A1 = #N/A
A2 = A1 + 5            # #N/A
A3 = SUM(A1, 1, 2)     # #N/A
A4 = IFERROR(A1, 0)    # 0   (explicit error-handling function)

Only dedicated error-handling forms (IFERROR, IFNA, TRY, DEFAULT/??, ?=, ISERROR, …) consume an error. Structural collection predicates are a narrow inspection exception: IN/HAS, SUBSET/SUPERSET, OVERLAPS, and ordered CONTAINS compare an error member by code without coercing it; diagnostic message text is not identity. Ordinary arithmetic and functions still pass errors through. Full rules: errors.md.

10. Gotchas

10.1 Blank is not empty string

A1 = ""        # empty string
A2 = BLANK     # blank
ISBLANK(A1)    # FALSE
ISBLANK(A2)    # TRUE
"" + 1         # 1   (empty string → 0 in arithmetic)

Both coerce to 0 in arithmetic and to falsy in boolean context, but ISBLANK distinguishes them.

10.2 Numeric strings coerce, words don't

"5" + 3        # 8
"5 apples" + 3 # #VALUE!   (not a clean number)

10.3 Boolean arithmetic is intentional

SUMPRODUCT((region = "EU") * amount)   # TRUE/FALSE → 1/0 enables masks

10.4 Don't rely on default number-to-text formatting

"Total: " & 1234.5            # "Total: 1234.5"
"Total: " & TEXT(1234.5, "$#,##0.00")   # "Total: $1,234.50"

11. See Also

• reference.md — kinds and type tags.
• assignments.md — tag checks.
• errors.md — error propagation and #TYPE!.
• functions.md — per-function argument contracts.