How to Use the ABS Function in Google Sheets
=ABS(value). =ABS(-26) returns 26.
What ABS does
The absolute value of a number is how far it is from zero, so -26 and 26 both have an absolute value of 26. In spreadsheets that is the quickest way to ask "how big is the difference?" without caring which side is bigger: planned versus actual, forecast versus result, this month versus last.
ABS syntax
=ABS(value)
Per Google's help page, value is the number whose absolute value is returned. The page gives these examples: =ABS(-26) returns 26, =ABS(-3.68) returns 3.68, and =ABS(-6/3) returns 2, since the division is calculated first.
A step-by-step example
Cell A2 holds a planned figure of 12 and B2 holds the actual figure of 5.
| A | B | |
|---|---|---|
| 1 | Planned | Actual |
| 2 | 12 | 5 |
=ABS(A2 - B2)
This returns 7. Here is why: 12 minus 5 is 7, and the absolute value of 7 is 7. If the figures were the other way round, the subtraction would give -7, and ABS would still return 7. That makes it safe to fill down a whole column of variances.
Common mistakes
- Hiding direction. ABS throws away the sign, so keep a separate signed column if you need to know whether the result was over or under.
- Forgetting parentheses around a calculation. Put the whole subtraction inside ABS.
- Passing text. It expects a number.
Try it yourself
Use the cells above (A2 and B2). Type your formula into the box, starting with =, then press Check.
Exercise 1: Size of -26
Write a formula that returns the absolute value of -26.
Exercise 2: The difference
Write a formula that returns the size of the difference between A2 and B2, ignoring the sign.
What to learn next
- TRUNC and INT: cut off decimals or round down to a whole number.
- ROUNDUP and ROUNDDOWN: always round up or always round down.
- MIN and MAX: find the smallest and largest values.
Syntax and rules in this guide were checked against Google's ABS help page. See all guides.