Variance analysis %change formula

[Salbee]

Hi all,

Not sure if you could assist.

I would like to calculate the variance in terms of %.

Example
1. this month 0,
next month 600,
Variance 100%

2. Last month 600
This month 0
Variance = - 100%

3. This month 0
Next month 0
Variance = 0%

4. This month =1
Next month=2
Variance =50%

Have even tried it in the pivot version and even the power query version, and also power BI.

Does not seems to get the results I want.

Could u advise how to get this done?

 

[alansidman]

=IF(AND(A2=0,B2>0),1,IF(AND(A2>0,B2=0),-1,IF(AND(A2=0,B2=0),0,(B2-A2)/A2))) and format as percentage

Book1
	A	B	C
1	This Month	Next Month	Varianc
2	0	600	100%
3	600	0	-100%
4	0	0	0%
5	500	600	20%
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=IF(AND(A2=0,B2>0),1,IF(AND(A2>0,B2=0),-1,IF(AND(A2=0,B2=0),0,(B2-A2)/A2)))