My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Give me a SIGN, Excel

You are here: Home / Excel Formulas / Give me a SIGN, Excel
Excel SIGN Function
by

The Excel SIGN function returns a 1 if the number is positive, a zero if it’s zero and a -1 if the number is negative.

The syntax is simple: =SIGN(number)

Here are some basic examples:

Excel SIGN Function Basic Example

So what’s it good for?

Well, last week we had a request for help from Alston. He was comparing the variances for Actual vs Forecast year on year.

This reminds me of my accounting days where one of my monthly tasks was to produce Actual Cost vs Budget/Forecast Cost reports.

Aside: Variances are calculated as =(Budget - Actual).

Our reporting standard was to present negative figures in brackets, and to pre-empt the question I received regularly from the department heads (who weren't finance people); "are negative variances good or bad?" I had a chant “remember, brackets are bad news” :-).

Brackets are Bad News

For my North American friends, brackets are the same as parentheses but saying “parentheses are bad news” just doesn’t have the same ring to it.

Occasionally I’d also throw in a “c’mon, Finance is Fun” line when I saw them frowning as I approached their office.

Finance is Fun

I’m pretty sure they laughed at me rather than with me, but at least they were smiling as I slipped the report on their desk (printing was big back then), or chased them up for their forecast figures, or requested an explanation for why they were 50% over budget and it was only March! 🙂

Ah, those were the days.

Excel SIGN Function Example

Let’s look at a clever use for the SIGN function which is along the lines of Alston’s question; here is a table of dummy variance data (Actual Cost vs Budget Cost variances) for 2013 and 2014 by department:

variances

Note: For the purpose of this we’ll assume that the budgeted figures for 2013 and 2014 are the same for each department so we can focus on comparing the change in variances.

With some high school math we can calculate the percentage change in variance for department A like so:

=(C2-B2)/B2)
=(-10 - -18)/-18)
=8/-18
=-44%

The problem here is the 2014 negative variance is lower than the 2013 negative variance so this should be an improvement year on year of 44% but the percentage is calculated as -44%.

Unfortunately it's not as simple as reversing the formula to subtract B2 - C2 etc. either.

So, in struts the SIGN function to the rescue:

=(C2-B2)/B2*SIGN(B2)
=44%

If we look at the other departments we can see the SIGN function returns the correct result each time:

Excel SIGN function example

And to make sure we handle #DIV/0! errors we can wrap the formula in an IFERROR Function:

=IFERROR((C2-B2)/B2*SIGN(B2),0)

Finally we can jazz it up a bit with some Custom Number formatting to indicate the direction with both colour and a symbol:

Customer Number Format

Download the Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Thanks

Thanks to Alston for his question and to Catalin (our in-house Excel Guru), for providing this clever solution which gave me the inspiration for this post.

Excel SIGN Function

More Excel Formulas Posts

excel formula by example
Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor
Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts
Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment
Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips
Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays
The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do
Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function
The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files
New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel
How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.


Category:

Reader Interactions

Comments

  1. ELINOR

    hello Ms Mynda, i just want to ask if there is a formula for selecting a breakdown of a certain total, for example:
    A1 – 10
    A2 – 80
    A3 – 50
    A4 – 60
    A5 – 90
    A6 – 100
    in these data there is total of 220, how can i know the breakdown of this total from the above data, is there any formula for this? Thank you and GOD BLESS.

    Reply

  2. Khushnood Viccaji

    One more thing I noticed is that while using the custom number formats, you can’t apply font-colour-based filters in the variance column.

    Excel just doesn’t ‘recognise’ the font colours in the cells, and so, in the AutoFilter drop-down the “Filter by colour” command is disabled. 🙁

    So if that’s a must for you (using font-colour-based filters), it would be better to use conditional formatting to apply the font colour, and use only the triangles in the custom number format.

    Reply

  3. Khushnood Viccaji

    Very interesting post Mynda 🙂

    I have already started using it in a few workbooks where I highlight the variance figures using conditional formatting.

    I also added a small tweak to the custom formatting, to make the coloured triangles aligned at the left or right edge of the cells. This helps to improve the readability of the variance figures.

    For aligning them along the left edge, the custom format is:
    [Color10]▲ * 0.0%” “;[Red]▼ * 0.0%” ”

    For aligning them along the right edge, the custom format is:
    [Color10]0.0%” “▲;[Red]0.0%” “▼

    Reply

  4. Jyler

    Thanks for sharing this Mynda. This is really helpful because it simplifies things for me. I used to create a long formulas in Excel to accomplish the improvements in negative variance… Very smart!

    Reply

    • Mynda Treacy

      @Mark and @Karen,

      To get the arrows first insert the arrow symbols into a cell (Insert tab > Symbol), then copy the symbol to the clipboard and paste it into your custom number format.

      Kind regards,

      Mynda

      Reply

  7. W

    I am really interested as to how you got the delta symbols in your custom format for the cells. I searched all through the symbols lists and only found the Greek letter “delta”. Could you please demonstrate this?

    Reply

    • Mark Dransfield

      Thanks for this post Mynda.
      Always great to visit an overlooked function. I wonder if it would be useful in vba?
      Just a hypothetical question: In the example you give, can anyone see a disadvantage in using Abs, as MF mentions above?
      Mark

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *