Math
ABS function: measure budget variance distance and tolerance bands
Strip minus signs with ABS so over-budget and under-budget variances do not cancel each other out and symmetric tolerance checks stay concise.
Prevent positive and negative variances from cancelling out
In a cost review, being $350 under budget on freight (-350) and $350 over budget on packaging (+350) yields a net sum of $0—hiding $700 of line-item deviation. ABS(number) returns non-negative distance from zero so both -350 and +350 evaluate to 350.
Summing or averaging absolute deviations measures total variance volume and Mean Absolute Error (MAE) accurately.
| Category (A) | Budget (B) | Actual (C) | Signed C-B | ABS(C-B) |
|---|---|---|---|---|
| Freight | 5000 | 4650 | -350 | 350 |
| Packaging | 3200 | 3550 | +350 | 350 |
| Insurance | 1800 | 1800 | 0 | 0 |
| Utilities | 2400 | 2160 | -240 | 240 |
Check symmetric +/- tolerance in a single test
Instead of writing OR(C2-B2 > 300, C2-B2 < -300), test ABS(C2-B2) > E2. Any deviation strictly further than 300 in either direction triggers "Review", while exact boundary matches (300 vs 300) remain "Within tolerance".
=IF(ABS(C2-B2)>E2, "Review", "Within tolerance")Compare net signed variance against total absolute variance
Across rows 2 to 5, net signed variance SUM(C2:C5)-SUM(B2:B5) is -240, whereas total absolute variance across the four lines is 350 + 350 + 0 + 240 = 940.
=ABS(C2-B2)+ABS(C3-B3)+ABS(C4-B4)+ABS(C5-B5)Try the example
Use the original values or change them to test your understanding.