Description
Use this function to determine the absolute value of a number. Supported in Chains.
This function converts negative numbers to positive numbers, positive numbers are unaffected.
Syntax
ABS(number)
Inputs
This function has the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
number |
Yes | The number whose absolute value is to be returned. | A cell reference, a number, or a formula which results in either of these. |
Examples
Sample data
| A | |
|---|---|
| 1 | 56 |
| 2 | 3+9 |
| 3 | -15 |
| 4 | 14.75 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Calculate the absolute value of a positive number. | =ABS(72) |
This formula works in the following manner:
This formula returns 72. |
| Calculate the absolute value of a number in a cell. | =ABS(A1) |
This formula works in the following manner:
For this data set, this formula returns 56. |
| Calculate the absolute value of a formula result. | =ABS(A2) |
This formula works in the following manner:
For this data set, this formula returns #VALUE!, because A2 contains the text string "3+9", not the numeric value "12". |
| Calculate the absolute value of the difference between two numbers. | =ABS(A1-14.74) |
This formula works in the following manner:
For this data set, this formula returns 41.26. |
Notes
- Wildcards don't work with this function.
Tips
ABS is useful when you are trying to:
- Calculate differences regardless of direction. When you need the magnitude of change but don't care about positive/negative; this is useful for variance analysis, temperature changes, or any bidirectional measurement.
- Find closest matches. You can combine ABS with MIN to find the closest value in a list:
=MIN(ABS($A$2:$A$10-B2))finds the smallest difference. - Use conditional formatting to highlight cells based on absolute deviation from a target, regardless of whether they're over or under.
- Work with time differences. For example, to calculate hours between times that might cross midnight:
=ABS(EndTime-StartTime)*24
Caution: Remember that ABS works on single values, not ranges. If you need to apply it to multiple cells, you'll need to either drag the formula down or use it within an array formula.