Formula Errors

Excel IF Formula to Return 2 or a Negative Difference

Maira MehtabMaira Mehtab Nov 18, 2026 869 views

Question details

Create a formula that returns 2 when two cell values are equal and a negative absolute difference otherwise.

Return 2 or a Negative Difference with an Excel IF Formula
Product
Microsoft Excel
Device & OS
not provided
Scenario
Compare two numeric values without regard to which one is larger.
Observed behavior
Equal values should produce 2; unequal values should produce the difference as a negative number.
Before you start

Identify the two cells containing the numeric values you want to compare. The example uses A1 and B1 and places the result in C1.

Solution 1Recommended

Use IF and ABS to Compare Two Excel Cells

Use this formula to return 2 for matching values and a negative difference for unequal values.

1
Select the result cell

In Excel, click C1 or another empty cell where you want the result.

2
Enter the formula

Type =IF(A1=B1,2,-ABS(A1-B1)) in the selected cell or formula bar, then press Enter.

3
Apply it to other rows

If needed, drag the fill handle at the bottom-right corner of C1 down to compare values in the rows below.

Use IF and ABS to Compare Two Excel Cells
How the formula works: IF returns 2 when A1 and B1 are equal. Otherwise, ABS calculates the absolute difference and the minus sign makes it negative.
Calculate in WPS Spreadsheet

Calculate a Negative Difference in WPS Spreadsheet

Use the same IF and ABS formula in WPS Spreadsheet to compare values and return 2 or a negative difference.

  1. 1. Open a spreadsheet: Open WPS Spreadsheet and select an existing workbook or create a new one.
  2. 2. Enter the values: Enter the numbers to compare in cells A1 and B1.
  3. 3. Add the formula: Click C1, enter =IF(A1=B1,2,-ABS(A1-B1)), and press Enter.
  4. 4. Copy the formula: Drag the fill handle from C1 down to apply the formula to additional rows.
Compare two values regardless of which is largerUse familiar IF and ABS functionsOpen and save Microsoft Excel XLSX filesWork with spreadsheets in a familiar interface
microsoft office alternative - wps office

Frequently Asked Questions

What does the formula return if A1 is 8 and B1 is 5?

The formula returns -3. Reversing the values also returns -3 because ABS removes the sign before the formula makes the difference negative.

How can I compare cells other than A1 and B1?

Replace the cell references in the formula. For example, use =IF(C2=D2,2,-ABS(C2-D2)) to compare C2 and D2.

Can I return a different number when the values match?

Yes. Replace 2 in the IF formula with the desired value. For example, =IF(A1=B1,0,-ABS(A1-B1)) returns 0 when the cells match.

Why does Excel reject the formula when I enter it?

Check that the formula starts with an equals sign and that both cell references are correct. If your regional settings use semicolons as argument separators, enter =IF(A1=B1;2;-ABS(A1-B1)) instead.