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:
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” :-).
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.
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:
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:
=(-10 - -18)/-18)
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:
If we look at the other departments we can see the SIGN function returns the correct result each time:
And to make sure we handle #DIV/0! errors we can wrap the formula in an IFERROR Function:
Finally we can jazz it up a bit with some Custom Number formatting to indicate the direction with both colour and a symbol:
Download the Workbook
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.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.