The ABS function
Returns the absolute value of a number, the number without its sign. -25 becomes 25 and 25 stays 25.
Syntax
ABS(number)- number
- The number, cell or calculation to take the absolute value of.
Examples
B1=ABS(-25)
| A | B | |
|---|---|---|
| 1 | 25 |
E1=ABS(C2-B2)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Budget | Actual | 150 | |
| 2 | Rent | 1200 | 1050 |
D1=SUMPRODUCT(ABS(B2:B5))
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Change | 305 | |
| 2 | Jan | 120 | ||
| 3 | Feb | -80 | ||
| 4 | Mar | 45 | ||
| 5 | Apr | -60 |
Questions
How do I turn negative numbers into positive numbers in Excel?
Wrap the value in ABS, as in =ABS(A2). Positive numbers stay as they are. To flip the sign of every number instead, multiply by -1.
How do I add up differences without them canceling out?
Use =SUMPRODUCT(ABS(B2:B5)). ABS turns every change positive first, so a drop of 80 counts as much as a rise of 80. SUM(ABS(B2:B5)) gives the same in Excel 365.