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