| Function category | Math and trigonometry |
| Volatility | Non-volatile |
See also, and don't confuse with: ABC analysis in Excel
What does the ABS function do
The ABS function returns the absolute value of a number. By definition, the absolute value is always a positive number. The absolute value of a negative number equals the number multiplied by -1.
ABS function syntax
=ABS(number)
| A | B | |
|---|---|---|
| 1 | Number | ABS |
| 2 | -5 | 5=ABS(A2) |
| 3 | 3.2 | 3.2=ABS(A3) |
| 4 | 0 | 0=ABS(A4) |
| 5 | -0.75 | 0.75=ABS(A5) |
| 6 | -1200 | 1200=ABS(A6) |
Negative numbers lose the minus sign, positive numbers and zero stay as they are. Text that can’t be read as a number returns #VALUE!.
ABS formula examples
Example 1
The table above was created by a call center manager to identify problematic hours in their operation.
The manager's task is to minimize human resources spent while maintaining maximum speed in answering incoming calls.
The table shows the average customer waiting time on the line. Both sharp increases from hour to hour and sharp decreases in this metric are bad signals.
Column D uses simple subtraction of the previous hour's value.
The manager uses the ABS function to identify hours when the difference is maximum, in order to adjust employee schedules and "smooth out" the graph.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Hour | Wait, sec | Change | Absolute change |
| 2 | 9:00 | 35 | ||
| 3 | 10:00 | 42 | 7=B3-B2 | 7=ABS(C3) |
| 4 | 11:00 | 40 | -2=B4-B3 | 2=ABS(C4) |
| 5 | 12:00 | 71 | 31=B5-B4 | 31=ABS(C5) |
| 6 | 13:00 | 66 | -5=B6-B5 | 5=ABS(C6) |
| 7 | 14:00 | 48 | -18=B7-B6 | 18=ABS(C7) |
| 8 | 15:00 | 52 | 4=B8-B7 | 4=ABS(C8) |
| 9 | 16:00 | 95 | 43=B9-B8 | 43=ABS(C9) |
Column C keeps the sign: a drop and a rise of the same size look different. ABS in column D puts them on one scale, so the biggest jumps (20+ seconds, highlighted) are easy to find — at 12:00 and 16:00.
Conditional formatting helps quickly identify that some employees should be kept for an extra hour at 16:00 on this day, and more employees should be scheduled at the very beginning of the shift.
Example 2.
The same manager uses a similar table but for all days of the week. To avoid cluttering the table with extra calculations, they use the ABS function in an array formula.
How does the calculation work in the formula? First, the absolute value is calculated for each array element using the ABS function, and then these absolute values are summed.
Like all array formulas, this formula is entered without curly braces, but the cell entry is done using Ctrl + Shift + Enter.
The formula calculates the cumulative value of "problematic" hours during each day and allows the manager to identify days with the most unbalanced schedule.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Day | 10:00 | 12:00 | 14:00 | 16:00 | 18:00 | Total jumps |
| 2 | Mon | 35 | 42 | 71 | 48 | 95 | 106=SUMPRODUCT(ABS(C2:F2-B2:E2)) |
| 3 | Tue | 40 | 41 | 44 | 43 | 45 | 7=SUMPRODUCT(ABS(C3:F3-B3:E3)) |
| 4 | Wed | 30 | 60 | 32 | 64 | 33 | 121=SUMPRODUCT(ABS(C4:F4-B4:E4)) |
C:F minus B:E gives the four hour-to-hour changes, ABS removes their signs, and the sum shows how uneven the day was. Tuesday is the smoothest; Wednesday swings the most. SUMPRODUCT works without Ctrl+Shift+Enter; in Excel 365 =SUM(ABS(C2:F2-B2:E2)) works as a normal formula too.
Other Math and trigonometry functions
SIGN, MMULT, ISODD, GCD, LCM, CEILING, FLOOR, ROUND, ROUNDUP, ROUNDDOWN, MROUND, MOD, TRUNC, PI, PRODUCT, ROMAN, RANDBETWEEN, RAND, POWER, SUM, SUMIF, SUMPRODUCT, FACT, INT, QUOTIENT, ISEVEN, COMBIN
See also:
Like the article? Help its author! Buy !SEMTools, it has lots of useful instruments to process text data.