Function guides · Math
ABS function
Returns the absolute value of a number, its distance from zero.
Syntax
=ABS(number)
| Argument | What it is |
|---|---|
number | A number, cell reference or formula result. |
What ABS does
ABS returns a number’s distance from zero, which means it removes a minus sign and leaves everything else alone. ABS(-7) is 7. ABS(7) is 7.
Its real use is on the difference between two values. A forecast that was 30 high and one that was 30 low are equally wrong, and ABS lets one formula treat them the same way.
It is a one-argument function with no options, which makes it easy to forget about until a threshold comparison goes wrong.
ABS is easy to misplace. The subtraction goes inside the brackets, so that the sign is removed from the difference rather than from one of the inputs. When you need to know the direction as well as the size, keep a second column with the signed difference, or use SIGN to return 1, -1 or 0.
A related habit is to put the tolerance in its own cell and refer to it. A rule such as “within 5 percent” changes more often than the formulas that use it, and a labeled cell keeps both the rule and the reason for it visible to whoever reads the sheet next.
ABS examples
1. Size of a variance
| A | B | C | |
|---|---|---|---|
| 1 | Budget | Actual | |
| 2 | 1000 | 880 | 120 |
Actual spend of 880 against a budget of 1,000 is a variance of -120. ABS drops the sign, so the result is 120, the size of the miss in either direction.
2. Check against a tolerance
| A | B | C | |
|---|---|---|---|
| 1 | Target | Measured | |
| 2 | 12 | 12.04 | Pass |
A part passes if its measured length is within 0.05 of the 12.00 target. ABS handles both too long and too short in one comparison.
3. Sum the sizes of mixed gains and losses
| A | B | |
|---|---|---|
| 1 | Change | |
| 2 | 40 | 75 |
| 3 | -25 | |
| 4 | 10 |
A plain SUM of +40, -25 and +10 gives 25. Summing absolute values gives the total movement, 75. SUMPRODUCT lets ABS work on a whole range without a helper column.
Common ABS mistakes
- Using ABS on the wrong step.
=ABS(B2)-A2is not the same as=ABS(B2-A2). Put the subtraction inside. - Hiding a direction you need. A variance report that shows only absolute values cannot tell over from under budget. Keep the signed value as well.
- Expecting ABS to work on ranges.
=ABS(A2:A4)spills in current Excel and fails elsewhere. Wrap in SUMPRODUCT or use a helper column.
ABS on Excel job tests
Variance and tolerance tasks use ABS even when they do not name it: "the absolute difference between forecast and actual", "flag any measurement more than 2 units from the target". Words like distance and deviation mean the same thing.
The usual structure is ABS of a subtraction inside an IF, with the threshold in its own cell. A frequent slip is comparing the signed difference to a positive limit, which lets every under-target row pass.
For a total of absolute values without a helper column, use SUMPRODUCT(ABS(range)). If the task lets you add columns, a helper column is easier to read.
Related functions
ABS questions
- What does ABS do in Excel?
- It returns the number without its sign: ABS(-5) is 5 and ABS(5) is 5.
- How do I find the difference between two numbers regardless of order?
- Use
=ABS(A2-B2). - Can ABS handle a whole range?
- In Microsoft 365 it spills one result per cell. For a single total use SUMPRODUCT(ABS(range)).
- Does ABS change text?
- No, text returns #VALUE!.
Practice ABS
The lesson Reading #REF!, #NUM!, #NULL!, #SPILL! and #CALC! (module 18 of Analyst) has checked ABS exercises. It is part of Full Access; only the first two lessons of each module are free. The free 5-item sample test uses the same kind of formulas in a working grid if you want to try before you decide.
Try a 5-item Excel test sample