If you find any post useful then, please do share with others. Thanks!

Popular Posts

Contact

Email me

Need help and that also free? I like to learn this way, in case of any question or for a small task, please feel free to email me with details and example data, if required.

Monday, August 24, 2015

Use of Excel SIGN Function

SIGN Function

The Excel sign function checks the sign of a number and gives result of 1 if the number is positive, 0 if number is zero and -1 if the number is negative.

Here we have a use of SIGN function. For example we have cost data of this year and last year and we are looking at % variance.

Please see in the below table when value is changing from -18 to -10 the normal calculation give us % change as -44%. Even though there is improvement in year over year number but the % is in negative. So we can use the SIGN function here to return the correct result each time.

A
B
C
D


Cost YE 2014
Cost YE 2015
Variance calculated with Sign function
Variance calculated by normal standard
Formula in Cell C
Formula in Cell D
-18
-10
44%
-44%
(B3-A3)/A3*SIGN(A3)
(B3-A3)/A3
10
12
20%
20%
(B4-A4)/A4*SIGN(A4)
(B4-A4)/A4
13
11
-15%
-15%
(B5-A5)/A5*SIGN(A5)
(B5-A5)/A5
-11
-10
9%
-9%
(B6-A6)/A6*SIGN(A6)
(B6-A6)/A6


No comments:

Post a Comment