Excel ABS & SIGN — Absolute Value, Magnitude, and the Sign You Throw Away

TL;DR — Every number carries two facts: how big it is (magnitude) and which way it points (sign). ABS(number) keeps the size and discards the direction — ABS(-7) and ABS(7) both return 7. SIGN(number) does the opposite: it keeps the direction as -1, 0, or +1 and discards the size. The headline use of ABS is comparing without caring about direction — a tolerance check is =ABS(A-B)<=tol, never =A-B<=tol. And the two functions combine into one tidy identity: number = SIGN(number) * ABS(number).

=ABS(-7)        ' -> 7      magnitude: distance from zero
=ABS(7)         ' -> 7
=SIGN(-7)       ' -> -1     direction only
=SIGN(0)        ' -> 0      three outcomes, not two
=ABS(Actual - Forecast) <= 0.05 * Forecast   ' within 5%? (direction ignored)

Most "why is my variance negative half the time?" and "my within-tolerance test lets huge errors through" problems come down to a single confusion: reaching for a number's value when you actually needed its magnitude. ABS and SIGN are the two functions that let you split those apart on purpose.

What you'll learn

The mental model: a number is magnitude × direction
Why a tolerance/variance check needs ABS(A-B), not A-B
What SIGN is really for: classification and crossing detection
The identity x = SIGN(x) * ABS(x) and where it pays off
Why SIGN(0)=0 is a three-way result you must handle
When not to use ABS — the sign it hides might be a bug

The mental model: magnitude × direction

Picture a number line. Any number is described by two independent things: its distance from zero (magnitude) and which side of zero it sits on (direction). ABS answers the first question and forgets the second; SIGN answers the second and forgets the first:

=ABS(-250)     ' -> 250    "how far from zero" — 250, direction dropped
=SIGN(-250)    ' -> -1     "which way" — negative, size dropped

Keeping these two questions separate is the entire skill. The moment you ask "how big is the gap between forecast and actual?" you want magnitude, and the sign is noise. The moment you ask "did this account gain or lose?" you want direction, and the size is noise. Bugs happen when you use the raw value — which mixes both — for a question that only wanted one.

The headline use: tolerance checks need ABS

This is the single most important pattern on the page. You want to flag when two numbers differ by more than some tolerance. The naive test is silently broken:

=IF(Actual - Forecast > 5, "off", "ok")          ' BUG
=IF(ABS(Actual - Forecast) > 5, "off", "ok")     ' correct

The first version only catches errors in one direction. If Actual comes in far below Forecast, Actual - Forecast is a large negative number, which is not greater than 5, so the check says "ok" while your number is wildly off. A difference you're testing for size must be wrapped in ABS, because you care how big the gap is, not which way it leans. The same logic drives mean absolute deviation — a cleaner spread measure than "average error," which cancels itself out:

=SUMPRODUCT(ABS(data - AVERAGE(data))) / COUNT(data)   ' mean absolute deviation

Without ABS, the positive and negative deviations cancel and you get ≈0 — the exact trap SUMPRODUCT is built to avoid when you feed it absolute values.

What SIGN is really for: classify and detect crossings

SIGN collapses any number to one of three tokens — -1, 0, +1 — which makes it a compact classifier and a change detector. Two uses justify its existence:

=SIGN(Change)                          ' -1 loss / 0 flat / +1 gain, in one column
=IF(SIGN(B3) <> SIGN(B2), "crossed", "")   ' flags where a series crosses zero

That second one is the clever use. A sign change between consecutive rows means the series passed through zero — a price crossing break-even, a balance going negative, a sensor reading flipping polarity. Comparing SIGN values catches the crossing with no messy AND(B2>0, B3<0) gymnastics. SIGN is also the natural partner for a directional multiplier: =Quantity * SIGN(Flow) applies +1/−1 without an IF.

The identity that ties them together

Here is the whole concept compressed into one line:

number = SIGN(number) * ABS(number)

Direction times magnitude reconstructs the original number. It looks like a curiosity until you need to rebuild a value from parts — most usefully, to take an odd root of a negative number, which raw exponentiation refuses (=(-8)^(1/3) is #NUM!, as the POWER & SQRT guide explains):

=SIGN(-8) * ABS(-8)^(1/3)     ' -> -2    cube root of a negative, sign preserved

You compute the root on the magnitude (which is always legal) and staple the original sign back on. That's the identity earning its keep.

SIGN(0) = 0 is a three-way result

Do not assume SIGN is binary. It returns three possible values, and the third — 0 for an input of exactly zero — is the one that breaks lazy code:

=IF(SIGN(x) = 1, "up", "down")     ' BUG: a flat 0 is mislabeled "down"
=IF(x > 0, "up", IF(x < 0, "down", "flat"))   ' handle all three

If you branch on SIGN, you must account for the 0 case, or just compare with >0 / <0 directly. It's the same discipline the IS functions teach: match the number of outcomes your test actually has.

When not to use ABS

ABS is so handy that people wrap it around differences reflexively — and that's where it turns from a tool into a cover-up. ABS is for when direction genuinely doesn't matter (tolerances, distances, magnitudes). If your differences are supposed to be positive and you slap ABS on to "clean them up," you've just hidden the rows where your logic is backwards. Reaching for ABS to make red numbers disappear is how you ship a model that's wrong in a way nobody can see. Two related distinctions worth keeping straight:

To floor negatives at zero, use MAX(x, 0), not ABS(x). MAX(-5,0) is 0; ABS(-5) is 5. Different intent — one clamps, the other mirrors.
To display a number as positive without changing its value (accounting style), use a number format, not ABS. ABS changes the stored value; a format only changes how it looks. Sorting and math see the real number.

The judgment call

Ask the question first, then pick the function. "How big is the gap / how far apart / what's the spread?" → magnitude → ABS. "Which way did it move / gain or loss / did it cross?" → direction → SIGN. "Both, recombined?" → the SIGN(x)*ABS(x) identity. The one habit that prevents the most damage is refusing to let ABS paper over a sign you didn't expect — if a difference comes out negative and that surprises you, investigate the logic before you wrap it. ABS should express intent, not suppress evidence.

How ExcelMaster helps

Tolerance logic is deceptively easy to get subtly wrong, and the bug passes every happy-path test. Tell ExcelMaster "flag rows where actual is more than 5% off forecast" and it writes ABS(Actual-Forecast) > 0.05*Forecast — with the ABS in place so under- and over-shoots are both caught. Ask for "gain/loss/flat in one column" and it uses SIGN with the zero case handled, not a two-way IF that silently mislabels break-even. It writes the intent you described, including the sign handling you'd have discovered only after the numbers looked wrong.

Frequently asked questions

How do I get the absolute value in Excel?

Use =ABS(number). It returns the magnitude — the distance from zero — so =ABS(-7) and =ABS(7) both return 7. To take the absolute value of a difference, wrap the whole expression: =ABS(A2-B2).

How do I make a number positive in Excel?

=ABS(number) turns any number positive by removing its sign. If instead you only want to replace negative numbers with zero (and leave positives alone), use =MAX(number, 0). And if you just want a negative number to display as positive without changing its value, apply a number format rather than a formula.

What does the SIGN function return in Excel?

=SIGN(number) returns -1 if the number is negative, 0 if it is exactly zero, and +1 if it is positive. It reports only direction, discarding magnitude — useful for classifying gains/losses or detecting where a series crosses zero (a sign change between two cells).

ABS keeps a number's magnitude and drops its sign (ABS(-7)=7); SIGN keeps its direction as -1/0/+1 and drops its size (SIGN(-7)=-1). Together they satisfy the identity number = SIGN(number) * ABS(number).

How do I check if two numbers are within a tolerance in Excel?

Compare the absolute difference to the tolerance: =ABS(A2-B2) <= tolerance. Using =A2-B2 <= tolerance without ABS only catches errors in one direction and lets large negative gaps pass as "within tolerance."

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-10.