• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

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

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course โ€“ Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Give me a SIGN, Excel

You are here: Home / Excel Formulas / Give me a SIGN, Excel
Excel SIGN Function
March 17, 2015 by Mynda Treacy

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.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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 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

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

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

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

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

New Array Shaping Excel Functions

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 and 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

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

Excel VSTACK and HSTACK Functions

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

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: Excel Formulas
Previous Post:Excel FiltersExcel Filters 4 Ways
Next Post:Excel Quick AnalysisExcel Quick Analysis

Reader Interactions

Comments

  1. ELINOR

    June 15, 2015 at 12:49 am

    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
    • Mynda Treacy

      June 15, 2015 at 9:12 am

      Hi Elinor,

      I presume you’re asking for the purpose of reconciliation. If so then you can use this add-in:

      https://www.summatch.com/

      There is no simple formula in Excel to do this type of calculation.

      kind regards,

      Mynda

      Reply
      • ELINOR

        June 15, 2015 at 6:40 pm

        Thank you very much Ma’am…

        Reply
  2. Khushnood Viccaji

    March 19, 2015 at 8:50 pm

    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

    March 19, 2015 at 8:33 pm

    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
    • Mynda Treacy

      March 19, 2015 at 8:43 pm

      Thanks, Khushnood!

      Great tip on the alignment. Thanks for sharing ๐Ÿ™‚

      Reply
      • Khushnood Viccaji

        March 19, 2015 at 8:51 pm

        My pleasure, Mynda ๐Ÿ™‚

        Reply
  4. Jyler

    March 18, 2015 at 8:19 am

    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

      March 18, 2015 at 8:40 am

      You’re welcome, Jyler. Glad you found it useful.

      Reply
  5. karen

    March 18, 2015 at 2:03 am

    Hi Mynda,

    How to enter the special symbols in the format cell ‘custom’

    Reply
  6. Mark McCormick

    March 18, 2015 at 1:37 am

    Hi,

    How did you add the up and down triangles in the custom number formatting?

    Reply
    • Mynda Treacy

      March 18, 2015 at 8:38 am

      @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

    March 18, 2015 at 12:58 am

    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
    • W

      March 18, 2015 at 1:44 am

      Found ’em !! in Arial > Geometric Shapes. All set now.

      Reply
  8. James Michael Perry

    March 17, 2015 at 9:56 pm

    Thanks to all. That’s a lot of sharing.

    Reply
    • Mynda Treacy

      March 17, 2015 at 9:57 pm

      Glad you liked it, James ๐Ÿ™‚

      Reply
  9. MF

    March 17, 2015 at 11:40 am

    Hi Mynda,
    Cool! Never thought of the use of SIGN for this kind of calculation. I used to put ABS to the base for calculating % change.
    https://wmfexcel.wordpress.com/2013/11/30/calculating-change-is-so-easy-to-make-a-mistake/
    Cheers,

    Reply
    • Mynda Treacy

      March 17, 2015 at 1:24 pm

      Me neither, MF. We have Catalin to thank for the SIGN tip ๐Ÿ™‚

      Reply
    • Mark Dransfield

      March 18, 2015 at 4:19 am

      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
      • Mynda Treacy

        March 18, 2015 at 8:39 am

        Hi Mark,

        You could use SIGN in VBA but the VBA equivalent function is called SGN.

        Kind regards,

        Mynda

        Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 ยท My Online Training Hub ยท All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.