DELTA

What it does

DELTA(number1, [number2])

DELTA(number1, [number2]) returns numeric one when the two numbers are equal and zero otherwise. The second number defaults to zero. It is an equality indicator, not an approximate-comparison function.

Try it

=DELTA(6,6) returns 1; =DELTA(6,7) returns 0; =DELTA(0) returns 1. Summing such results counts exact matches.

Quirks

The recorded wrapper rejects logicals in either position, including TRUE held in a cell, with #VALUE!. A referenced blank instead reads as zero. Errors reveal the read order: DELTA(TRUE,#REF!) returns #REF!, while DELTA(#REF!,TRUE) returns #VALUE!, because the second scalar is read first.

Equality applies to the represented inputs, with no tolerance. Mathematically equal decimal expressions can arrive as different binary64 values. A tolerance comparison is a different operation.

How to implement

Read and convert the supplied second scalar first, or use zero when it is omitted. Reject a logical and return an error encountered in that position before reading the first scalar. Then read the first scalar under the same nonlogical numeric rules. A referenced blank is numeric zero.

Compare the admitted numbers for equality and return numeric one or zero, rather than logical TRUE or FALSE. For (6,6) the indicator is one. If both inputs are errors, the recorded result is the second error. This read order is part of the implementation; validating left to right changes the answer before the equality test is reached.

Rust code

70 delta "DELTA" (1, 2) [V "number1", V "number2"] |s, a, sv| {
    // Excel reads the second scalar before the first, including conversion errors.
    let y = if given(a, 1) { super::kit::num_without_logical(s, a, 1)? } else { 0.0 };
    let x = super::kit::num_without_logical(s, a, 0)?;
    out(s, k::delta_fn::delta_kernel(x, y))
}

pub fn delta_kernel(lhs: f64, rhs: f64) -> Result<f64, WorksheetErrorCode> {
    Ok(if lhs == rhs { 1.0 } else { 0.0 })
}

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

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

--(a=b) expresses a worksheet equality indicator; GESTEP tests an inclusive threshold; COUNTIF counts matches over a range.

Named variants

No named variants are recorded yet.