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.

DELTA results for sample pairs
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

Compare text, including case and spaces, with EXACT