Less than (<)

What it does

value1 < value2

A < B returns TRUE when A precedes B in Excel's comparison order, and FALSE otherwise.

The three shared comparison rules are stated in full on FUNC.OP_EQUAL. This operator is where the first of them stops being an abstraction, because ordering across kinds is only observable through the strict inequalities:

   any Number   <   any Text   <   any Logical

Within a kind:

• Numbers order numerically, but through the family's normalization lane rather than exact IEEE comparison — the contract records =0.1+0.2<0.3 as FALSE, which exact arithmetic would make TRUE. See FUNC.OP_EQUAL rule 3.
• Text orders by a case-insensitive comparison. The collation beyond that — accents, non-Latin scripts, locale-specific sequences — is explicitly out of the contract's slice, and the Handbook has measured only the cases in FN-OP_EQUAL-001, FN-OP_LESS_THAN-001 and FN-SORT-001.
• Logicals order FALSE before TRUE.

Arguments

Arity is exactly 2; no optional arguments, no defaults. Order is load-bearing — this is the first comparison page where swapping operands changes the answer.

Try it

Quirks

Result and edge cases

• Equal operands give FALSE; < is strict.
• Different kinds give the kind ordering, always, with no numeric interpretation of text.
• Blank operands. The context-sensitive blank rule applies: blank behaves as 0 against a number, "" against text, FALSE against a logical. So a blank cell is not less than zero, and is not less than "" — but it is less than any positive number and less than TRUE.
• Text ordering of numeric strings. "10" < "9" is TRUE under text ordering while 10 < 9 is FALSE; the difference is entirely in whether the operands are text. Sorted columns of numbers stored as text are the standard way this reaches a real workbook.
• Arrays. Ordinary broadcast with #N/A for unsupplied coordinates.

Errors

data/functions/FUNC.OP_LESS_THAN.json records no Microsoft documentation URL (docs is null).

How to implement

Notes for implementers

• Implement one comparison core returning a three-way result (less, equal, greater, plus an error lane), and derive all six operators from it. Six independent implementations will disagree somewhere, and the disagreement will be invisible until a user finds it.
• The kind ordering must be applied before any within-kind comparison, and must be total.
• Case-insensitive text comparison and locale collation are not the same problem. Record which one you implemented; the contract's own slice stops at the installed baseline.
• Do not reduce A<B to NOT(A>=B) in the presence of errors — the error must reach the result, not become a logical.

Rust code

CatalogueEntry::operator(LESS, "<", Notation::Infix, VALUES, less)

fn less(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    compare(LESS, arguments, services, Want::Order, Ordering::is_lt)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• FUNC.OP_LESS_EQUAL — the non-strict form.
• FUNC.OP_GREATER_THAN — the mirror. A<B and B>A should agree; that they do is an assumption worth testing rather than a theorem.
• FUNC.OP_EQUAL — the rules; also the third leg of trichotomy.
• MIN, SMALL, SORT, RANK — functions that order values. They do not necessarily use this operator's rules; MIN over a range ignores text entirely, where < ranks it above every number. Do not reason from one to the other.
• MATCH with match type 1 or -1, and VLOOKUP/HLOOKUP in approximate mode, depend on a data ordering that must agree with the comparison ordering. Mixed-kind columns are where that assumption breaks.
• COUNTIF(range,"<5") — criteria strings again: parsed by the consuming function, not this operator.

Named variants

No named variants are recorded yet.