Excel DELTA Function
last modified October 5, 2026
The DELTA function is an engineering function in Excel that tests
whether two numbers are equal. It returns 1 when the values match and 0 when
they do not. Because the result is numeric, a set of DELTA results
can be added together to count equal pairs. This tutorial covers the syntax,
the default value of the second argument, equality testing, counting exact
matches, array use, and the relationship to GESTEP.
DELTA Function Basics
DELTA compares two numeric values for equality. It belongs to
Excel's engineering functions and is also known as the Kronecker delta. The
syntax is short, but the default behavior of the second argument is worth
remembering.
| Component | Description |
|---|---|
| Function Name | DELTA |
| Syntax | =DELTA(number1, [number2]) |
| Arguments | number1 (required), number2 (optional, defaults to 0) |
| Return Value | 1 if the two numbers are equal, 0 otherwise |
The table above lists the essential components of the function. The argument table below describes each argument in more detail.
| Argument | Meaning |
|---|---|
| number1 | The first number. It can be a literal, a cell reference, or an expression that returns a number. |
| number2 | The second number, tested against number1. If omitted, it
is assumed to be 0, so =DELTA(A1) checks whether A1 is
zero. |
Both arguments must be numeric. Text, including text that looks like a number,
produces a #VALUE! error. A reference to an empty cell is read as
0, so an omitted argument and a blank cell behave the same way.
Basic DELTA Example
This example demonstrates the simplest use of the function with two equal numbers.
=DELTA(5, 5)
The formula compares 5 with 5. Because the values are equal, the result is 1.
The same formula with references, =DELTA(A1, B1), returns 1
whenever the two cells hold the same number.
Testing Inequality
When the values differ, DELTA returns 0. The result is always
exactly 1 or 0, which makes the output easy to count and compare.
=DELTA(10, 20) ' 0 =DELTA(5, 4) ' 0 =DELTA(0.5, 0) ' 0
The first formula compares 10 with 20 and returns 0. The second is the example from the Microsoft documentation: 5 does not equal 4, so the result is 0. The third compares 0.5 with 0 and also returns 0.
DELTA with a Single Argument
The second argument is optional. When it is left out, DELTA
compares number1 against zero. This makes the function a compact
zero test.
| A | B | Result |
|---|---|---|
| 0 | =DELTA(A1) | 1 |
| 5 | =DELTA(A2) | 0 |
| 0.5 | =DELTA(A3) | 0 |
The table shows that only the zero value produces a match. Any other number, including a fraction, is compared with 0 and returns 0.
=DELTA(A1) ' 1 when A1 contains 0, otherwise 0
DELTA with Cell References
DELTA is most often used with cell references. This example
compares corresponding values in two columns.
| A | B | C |
|---|---|---|
| 100 | 100 | =DELTA(A1, B1) |
| 200 | 150 | =DELTA(A2, B2) |
The first row compares 100 with 100 and returns 1. The second row compares 200 with 150 and returns 0. Column C therefore holds a 1 for every matching pair and a 0 for every mismatch.
=DELTA(A1, B1)
Testing Equality of Decimals
DELTA compares the stored values exactly. Two numbers that display
the same can still be unequal if they differ far beyond the visible digits,
which is a common source of surprise with floating-point arithmetic. Rounding
both values first removes most of that risk.
=DELTA(3.14159, 3.14159) ' 1, identical values =DELTA(ROUND(3.1415, 2), ROUND(3.1425, 2)) ' 1, both round to 3.14
The first formula compares two identical decimals and returns 1. The second rounds both values to two decimal places before comparing them; both become 3.14, so the result is 1. When the values come from calculations, rounding or a tolerance test is safer than comparing the raw results.
Counting Exact Matches
Because the result is a number, several DELTA results can be added
to count how many pairs are equal. This is the use case that Microsoft
highlights in the function description.
| A | B |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 4 |
=SUM(DELTA(A1:A3, B1:B3)) ' 2
The formula compares each row in turn and totals the results. The first two pairs match, while the third does not, so the count is 2. This is a convenient way to score two lists against each other without writing a helper column.
DELTA with Arrays
DELTA accepts array arguments and returns an array of 1s and 0s.
In Microsoft 365 and Excel 2021 the results spill automatically when the
formula is confirmed with Enter. In Excel 2019 and earlier the formula is an
array formula and must be confirmed with Ctrl+Shift+Enter.
=DELTA({1,2,3}, {1,2,4}) ' {1,1,0}
=SUM(DELTA({1,2,3}, {1,2,4})) ' 2
The first formula compares three pairs and returns the array
{1,1,0}. The second wraps the same comparison in
SUM to count the matches, returning 2. The curly braces in these
examples are array constant syntax, typed literally for the constants. Excel
adds braces automatically only when an array formula is confirmed with
Ctrl+Shift+Enter.
DELTA and GESTEP
GESTEP is the companion engineering function. It returns 1 when a
number is greater than or equal to a threshold and 0 otherwise, so it tests an
inequality rather than equality.
| Function | Test | Example | Result |
|---|---|---|---|
| DELTA | number1 = number2 | =DELTA(5, 5) | 1 |
| DELTA | number1 = number2 | =DELTA(5, 4) | 0 |
| GESTEP | number >= step | =GESTEP(5, 4) | 1 |
| GESTEP | number >= step | =GESTEP(-1) | 0 |
GESTEP also treats an omitted step as 0, so
=GESTEP(-1) asks whether -1 is greater than or equal to 0 and
returns 0. For integer values the two functions are linked:
=GESTEP(n, k) - GESTEP(n, k+1) equals
=DELTA(n, k), because a value reaches k but not
k+1 only when it is exactly k.
=GESTEP(5, 4) - GESTEP(5, 5) ' 0, same as DELTA(5, 4) =GESTEP(5, 5) - GESTEP(5, 6) ' 1, same as DELTA(5, 5)
DELTA with Text and Blank Cells
DELTA works only with numbers. Text is rejected even when it looks
numeric, and a blank cell is read as 0. The table summarizes the behavior.
| Input | Result |
|---|---|
| 5 (number) | Compared normally |
| "5" (text that looks numeric) | #VALUE! |
| "abc" (text) | #VALUE! |
| Empty cell reference | Treated as 0 |
=DELTA("5", 5) ' #VALUE!, text is not accepted
=DELTA(A1, B1) ' 1 when both cells are empty
Convert numeric text with VALUE before passing it to
DELTA when the source cannot be controlled. A reference to an
empty cell is treated as 0, so two empty cells compare as equal.
DELTA vs. the Equal Operator
The = operator also tests equality, but it returns a logical value
rather than a number and it compares text without error.
| Feature | DELTA | = Operator |
|---|---|---|
| Output | 1 or 0 | TRUE or FALSE |
| Summing matches | Add the results directly | Convert with -- or N first |
| Text arguments | #VALUE! error | Compares text (not case sensitive) |
| Array arguments | Returns an array of 1s and 0s | Returns an array of TRUE and FALSE |
Choose DELTA when you want a numeric flag that can be summed or
multiplied without conversion. Choose the = operator when you need
a logical result or want to compare text.
Practical Applications
DELTA is useful wherever an exact match must be turned into a
number.
- Counting how many measurements match a target or a reference run
- Flagging mismatches between expected and entered values
- Scoring two lists row by row without a helper column
- Building binary flags for downstream calculations
- Feeding match counts into quality-control and signal-processing sheets
The numeric output integrates cleanly with SUM,
SUMPRODUCT, and ordinary arithmetic, which is why it appears in
engineering templates.
DELTA Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| An argument is text | #VALUE! error | Convert it with VALUE, or correct the source cell |
| An argument refers to a cell holding an error | The same error is returned | Fix the upstream error; DELTA cannot mask it |
| Two values that look equal are reported as unequal | Result 0 for values that appear identical | Round both values with ROUND before comparing |
| Array formula returns a single value in older Excel | Only the first pair is evaluated | Confirm with Ctrl+Shift+Enter, or wrap it in SUM |
| No arguments supplied | "too few arguments" error | Supply at least number1; number2 defaults to 0 |
The DELTA function turns an equality test into the numbers 1 and 0.
That small detail is what makes it valuable: matches can be counted, averaged,
and combined with other results as easily as any other number. Remember that
the second argument defaults to 0, that text is rejected rather than coerced,
and that exact comparison of calculated decimals can fail, so round first when
precision is in doubt. Used with SUM and its companion
GESTEP, DELTA becomes a compact tool for scoring and
filtering data.
Author
List all Excel Formulas.