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