How to Show the Direction of Change with SIGN

SIGN returns 1, 0 or -1 — a compact way to label metrics as up, flat or down and score a whole dashboard.

You will learn to

• Return the direction of a change
• Turn the direction into a label
• Score all metrics in one formula

How it works

SIGN(number) returns 1 if the number is positive, -1 if it is negative and 0 if it is zero. Applied to this month minus last month, =SIGN(C2-B2) tells you the direction of change in one tidy value.

Because the result is always -1, 0 or 1, it is easy to build on: =CHOOSE(SIGN(C2-B2)+2,"Down","Flat","Up") turns it into a label, and =SUMPRODUCT(SIGN(C2:C8-B2:B8)) counts how many metrics rose minus how many fell.

=SIGN(C2-B2)