Excel / Compare Two Numbers with the DELTA Function
Excel’s DELTA function returns 1 if two numbers are equal and 0 if they differ. Omit the second argument from the formula to compare the first number with zero. The numeric result makes it easy to total matching rows.
DELTA syntax and the second argument
The first argument is required. The first formula compares A2 with B2, and the second compares A2 with zero. For example, DELTA(0) gives 1 and DELTA(5) gives 0.
=DELTA(A2,B2)
=DELTA(A2)
An argument that cannot be evaluated as a number may produce a #VALUE! error. Use the comma or semicolon separator required by your Excel regional settings.
Microsoft’s DELTA function reference
Compare two columns row by row
Enter the first two columns in A2:B5. Put the two-value formula in C2 and fill down to C5.
| Number in column A | Number in column B | DELTA result |
|---|---|---|
| 5 | 5 | 1 |
| 5 | 6 | 0 |
| 0 | 0 | 1 |
| -2 | -2 | 1 |
The results from C2 are 1, 0, 1, and 1. Two zeros match, as do two identical negative values.
Count matching rows
Add the ones and zeros in the result column to count matches. Summing C2:C5 in the example gives 3.
=SUM(C2:C5)
If an empty cell has a different meaning from zero, check that both inputs contain numbers first. Flag incomplete rows separately or exclude them from the count.
Compare decimals with a tolerance
Computed decimals can differ slightly because of floating-point representation. Values displayed with the same digits may have different stored values and return 0 with DELTA.
To treat a difference below 0.000001 as a match, enter 10 in A7 and 10.0000005 in B7, then apply the following formula.
=ABS(A7-B7)<0.000001
The condition returns TRUE because the difference is within the tolerance. DELTA returns 0 for the same pair. Set the tolerance according to the units and precision your data requires.
Microsoft’s explanation of floating-point arithmetic in Excel




