What it does
ABS takes a single argument: it returns a positive number unchanged and a negative number without its minus sign; 0 stays 0. =ABS(C2-B2) tells you how far apart two values are, whichever one is larger.
It is common in tolerance checks: =IF(ABS(C2-B2)<=2,"OK","Check") flags rows where the counted stock and the system stock differ by more than 2, whether the difference is up or down.
To add up the absolute differences of a range, use =SUMPRODUCT(ABS(C2:C9-B2:B9)); SUMPRODUCT handles the range without a helper column. To show the direction separately, use SIGN, which returns 1 for a surplus and -1 for a shortfall.
Syntax
=ABS(number)=MUTLAK(sayı)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
number | Required | The number, cell or formula (such as C2-B2) whose sign is dropped. |
Examples
1Measure budget variance without the minus sign
Column D shows how far each item is from budget, whatever the direction; column E divides the variance by the budget. Items under budget show a positive variance too.
=ABS(C2-B2)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Budget | Actual | Variance | Variance % |
| 2 | Staff | 420,000 | 431,500 | 11,500 | 2.7% |
| 3 | Rent | 90,000 | 90,000 | 0 | 0.0% |
| 4 | Marketing | 60,000 | 48,200 | 11,800 | 19.7% |
| 5 | Travel | 25,000 | 31,750 | 6,750 | 27.0% |
| 6 | Training | 18,000 | 16,400 | 1,600 | 8.9% |
- D2 (filled down to D6)
=ABS(C2-B2) - E2 (filled down to E6)
=D2/B2
Marketing is under budget, yet its variance shows as 11,800, or 19.7%. The largest relative variance is Travel: 27.0%.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Check count differences against a tolerance
A difference of up to 2 units between the counted stock and the system stock is accepted. With ABS, shortfalls and surpluses are tested by the same rule.
=IF(ABS(C2-B2)<=2,"OK","Check")| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | System | Counted | Status |
| 2 | Grounded socket | 250 | 248 | OK |
| 3 | Circuit breaker | 120 | 125 | Check |
| 4 | Light switch | 300 | 301 | OK |
| 5 | Cable duct | 80 | 74 | Check |
- D2 (filled down to D5)
=IF(ABS(C2-B2)<=2,"OK","Check")
The socket (2 short) and the switch (1 over) get OK; the breaker (5 over) and the cable duct (6 short) get Check.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Net total versus absolute total of differences
The net difference lets positive and negative variances cancel out; the absolute total adds each row's difference without the sign and shows how far off the forecast really was.
=SUM(C2:C5)-SUM(B2:B5)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Month | Forecast | Actual | Net Difference | Absolute Difference | ||
| 2 | January | 1,200 | 1,350 | 0 | 540 | ||
| 3 | February | 1,100 | 980 | ||||
| 4 | March | 1,300 | 1,420 | ||||
| 5 | April | 1,250 | 1,100 |
- F2
=SUM(C2:C5)-SUM(B2:B5) - G2
=SUMPRODUCT(ABS(C2:C5-B2:B5))
The net difference looks like only 0, because surpluses and shortfalls cancel out. The absolute difference is 540, which shows how far off the forecast really was.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #VALUE!
Cause: number is text that cannot be read as a number.
Fix: Make sure the cell really contains a number.
- Direction is lost
Cause: ABS removes the information about whether the variance is above or below budget.
Fix: Keep the direction in a separate column:
=IF(C2>B2,"Over","Under").
Good to know
- If you only want negative numbers to appear without the minus sign, use a number format and leave the value alone; ABS changes the value itself.
- To find the largest variance,
=MAX(D2:D9)is enough when column D was already calculated with ABS.
In Nexell
Get Nexell to do it from a sentence
Add a Variance column that shows the difference between budget and actual without a minus sign, and flag rows above 10%
Convert to tableBring your budget table into the tool and type this. Nexell builds the variance and flag columns with real Excel formulas and shows them in the preview; in the downloaded file, the flags update when the figures change.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I make negative numbers positive in Excel?
Use =ABS(A2) in a new column. To flip every sign instead, type -1 in an empty cell, copy it, select the numbers and use Paste Special > Multiply; note that this also turns positive numbers negative.
How do I sum the absolute differences?
=SUMPRODUCT(ABS(C2:C9-B2:B9)) adds each row's difference without the sign. A plain =SUM(C2:C9)-SUM(B2:B9) lets positive and negative differences cancel out.
Related functions
- SIGNİŞARET · Sign of a number.
- SUMPRODUCTTOPLA.ÇARPIM · Multiplies matching items of arrays and sums them; the classic conditional sum: SUMPRODUCT((City="Ankara")*Amount).
- IFEĞER · Returns one value if a condition is true and another if false.
- ROUNDYUVARLA · Rounds a number to a number of digits.
- MAXMAK · Largest value.