How can I use SIGN() to display trend icons in a dashboard?

Combine SIGN with IF or CHOOSE: =CHOOSE(SIGN(A1)+2, '▼', '–', '▲') returns a downward arrow for negative, a dash for zero, and an upward arrow for positive values.

Can SIGN() be used to audit Excel models for sign errors?

Yes - if a cell should always be positive (like a cost value), use =IF(SIGN(A1)=-1,'ERROR','OK') to flag any cell where the value is unexpectedly negative.

Excel SIGN Function: Check Positive or Negative

This post features one of the lesser known Excel functions - the =SIGN function. The =SIGN function can be used to confirm if the value in a cell is positive, negative or zero. It returns a 1 if the v...

This post features one of the lesser known Excel functions - the =SIGN function.

The =SIGN function can be used to confirm if the value in a cell is positive, negative or zero. It returns a 1 if the value is positive, -1 if the value is negative, or zero if the value is zero.

The applications of this function are varied - for instance, it can be applied as a check in progress dashboards to return a downward triangle if the values are negative, or an upward triangle if the values are positive.

The below image shows the two steps taken to achieve the progress icon view above:

It could also be used as an Excel audit feature, to point out errors or discrepancies in cells that would be expected to show only positive or only negative values to ensure proper functioning of a model.

What other applications could this function come in handy for? Share in the comments section below:

Use Excel's SIGN() Function to Check Positive, Negative or Zero Values

Frequently Asked Questions