What ABS Does
The ABS function returns the absolute value of a number — its distance from zero on the number line, always as a non-negative result. Positive numbers stay the same, negative numbers become positive, and zero remains zero. It is widely used when you care about the magnitude of a difference but not its direction, such as measuring variance, calculating error margins, or finding the gap between two values regardless of which is larger.
Syntax
=ABS(value)
| Parameter | Description |
|---|---|
| value | The number or cell reference whose absolute value you want. |
Basic Examples
Convert a negative number to positive
| A | B | |
|---|---|---|
| 1 | Value | Absolute |
| 2 | -45 | =ABS(A2) |
| 3 | 23 | =ABS(A3) |
| 4 | 0 | =ABS(A4) |
Results in column B:
| Absolute |
|---|
| 45 |
| 23 |
| 0 |
Find the size of a price change
| A | B | C | |
|---|---|---|---|
| 1 | Item | Change ($) | Magnitude |
| 2 | Stock A | -12.50 | =ABS(B2) |
| 3 | Stock B | 8.75 | =ABS(B3) |
Results:
| Magnitude |
|---|
| 12.50 |
| 8.75 |
You can now sort by magnitude to find the biggest movers without worrying about whether they went up or down.
Calculate the gap between two numbers
| A | B | C | |
|---|---|---|---|
| 1 | Budget | Actual | Variance |
| 2 | 5000 | 4200 | =ABS(A2 - B2) |
| 3 | 3000 | 3800 | =ABS(A3 - B3) |
Results:
| Variance |
|---|
| 800 |
| 800 |
Both rows show a gap of 800, regardless of whether spending was over or under budget.
Advanced Examples
Find the closest match in a list
If you have a target value and want to find the nearest entry in a column, use ABS with MIN and MATCH:
=INDEX(A2:A10, MATCH(MIN(ABS(A2:A10 - D1)), ABS(A2:A10 - D1), 0))
This is an array formula (press Ctrl+Shift+Enter or it may auto-detect). It calculates the distance from each value in A2:A10 to the target in D1, finds the minimum distance, and returns the corresponding value. Useful for snapping user input to the nearest valid option.
Measure percentage deviation
To calculate how far each month's sales deviated from the average, expressed as a positive percentage:
=ABS(B2 - AVERAGE(B$2:B$13)) / AVERAGE(B$2:B$13)
For an average of 10,000 and a monthly value of 7,500, this returns 0.25 (or 25%). Wrapping in ABS ensures months below average produce the same magnitude as months above.
Common Mistakes
- Using ABS when you actually need to preserve the sign. If you need to know whether the variance is positive or negative (over vs. under budget), do not use ABS. Only apply it when direction is irrelevant and you want magnitude alone.
- Wrapping ABS around text or non-numeric cells.
=ABS("hello")returns a#VALUE!error. Make sure the input is a number or a cell containing a number. Guard against text with ISNUMBER if needed:=IF(ISNUMBER(A1), ABS(A1), "N/A"). - Forgetting ABS in conditional formatting rules. When highlighting cells where the variance exceeds a threshold, forgetting ABS means negative variances might not trigger the rule. Use
=ABS(B2 - C2) > 100as the custom formula for the formatting rule.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English and the formula is generated for you. Install SheetAI to try it free.