DELTA in WPS Spreadsheet: Count Exact Numeric Matches and Audit Pairwise Controls
Use WPS DELTA for numeric equality, an omitted zero comparison and complete reconciliation matrices, while distinguishing exact equality from an explicit tolerance.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: DELTA returns numeric 1 when two numbers are equal and 0 when they differ. =DELTA(30,31) returns the source’s documented 0. If the second argument is omitted, WPS specifies zero as the comparison value.
Availability and version history
- Introduced: documented by 2019-06-24 in WPS material; the first release is not established.
- Availability: current WPS Windows documentation identifies the function as built-in; the historical lesson has a blank edition field.
- Scope: exact supported builds and other WPS clients remain unverified.
- Checked: October 14, 2026. Documentation and independent examples; no native WPS execution.
Syntax and numeric output
=DELTA(number1,[number2])
Number1 is required; number2 is optional and defaults to zero. The official WPS lesson calls this the Kronecker delta function and explicitly suggests summing several results to count equal pairs.
The output is a numeric flag, distinct from a logical TRUE/FALSE comparison. A one means equality, not a difference of one between the inputs. The formal notes assign #VALUE! when either argument is nonnumeric.
Example 1: reconcile two numeric columns
Enter only columns A and B in A1:B6, including the headings. In C1 type Match flag, then put =DELTA(A2,B2) in C2 and fill through C6.
| A, recorded | B, checked | C, expected flag |
|---|---|---|
| 30 | 31 | 0 |
| 42 | 42 | 1 |
| -5 | -5 | 1 |
| 0 | 0 | 1 |
| 18 | 20 | 0 |
=SUM(C2:C6) gives 3 exact matches. There are five comparisons, so =5-SUM(C2:C6) gives 2 mismatches.
The two quantities −5 match despite being negative. The zeros also match. DELTA is comparing values, not asking whether either value is positive or nonzero.
For reader-friendly labels, put =IF(C2=1,"Match","Review") in D2 and fill down. Expected labels are Review, Match, Match, Match, Review. Keep the numeric flags if subsequent counting is useful.
Example 2: use the omitted zero comparison
Enter 0, 4, −4 and 0 in F2:F5. In G2 use =DELTA(F2) and fill down. The expected results are 1, 0, 0 and 1.
=DELTA(F2,0) expresses the same documented comparison explicitly. Summing G2:G5 gives 2 zero-valued entries in this numeric fixture.
This example tests numeric zeros. It does not establish how a blank cell, a formula-generated empty string or the text character zero is coerced. Those inputs need their own product-specific evidence before being included in a count policy.
Advanced example: construct a complete matching matrix
Enter row keys 10, 20 and 30 in I2:I4, and the same three column keys across J1:L1. Put this formula in J2 and fill it across J2:L4:
=DELTA($I2,J$1)
The complete expected three-by-three output is:
| Row key | Column 10 | Column 20 | Column 30 |
|---|---|---|---|
| 10 | 1 | 0 | 0 |
| 20 | 0 | 1 | 0 |
| 30 | 0 | 0 | 1 |
The dollar signs preserve the row-key column I and the column-key row 1 while the other coordinate changes. Summing the output J2:L4 gives 3. Each row and each column contains exactly one match because the keys are unique.
Now change L1 from 30 to 20. The output rows become 1,0,0, 0,1,1 and 0,0,0. The overall sum is still three, but row twenty has two matches and row thirty has none. A total alone cannot prove one-to-one matching. Inspect row or column subtotals when duplicate keys matter.
Advanced example: make tolerance an explicit different policy
Exact equality and closeness are different requirements. Enter 10.001, 10.006 and 10.020 in N2:N4, and put 10 in O2:O4. DELTA on each pair has independently expected flag 0, because none of the supplied decimal values equals ten.
If the application intentionally accepts differences below 0.01, use:
=IF(ABS(N2-O2)<0.01,1,0)
Filled down, the expected flags are 1, 1 and 0. These examples stay away from the exact tolerance boundary. ABS measures the magnitude of the difference, and IF turns the explicitly chosen condition into a flag.
This tolerance formula is a separate rule, not a DELTA option. Document why 0.01 and a strict less-than comparison are appropriate before using them in a reconciliation.
Errors and numerical precision
The lesson explicitly states nonnumeric arguments produce #VALUE!, but does not settle numeric-looking text, Boolean or empty-reference coercion. Use actual numeric inputs for the reproducible examples above.
Numbers that look equal after display rounding can still have different stored values. Conversely, an exact zero difference may be a valid calculated result. This guide does not claim a target-native outcome for a binary floating-point boundary such as =DELTA(0.1+0.2,0.3). Inspect the upstream calculation and decide whether exact equality, rounding or a tolerance is the intended policy.
References and verification
- WPS Academy: DELTA, 2019-06-24 (Chinese).
- Current WPS Windows DELTA introduction.
- Editorial testing standards.
Complete function-specific sources reviewed. Worked results are independently derived expectations; no native WPS execution was performed. Formulas use English names and comma separators. Check the installed locale before entering them.