Variance if both cells are zero or if > zero

[JustDebbie5]

Every quarter, I review the variances between the current and previous quarters. I need two parts to a formula to review these accurately, and I don't know how to write it. If both quarters are zero, then I would like the variance percentage to populate 0%. But if both quarters are >0, then populate appropriate percentage. This obviously isn't the appropriate formula, but it would be something like IF P6=0 and Q6=0, then 0%; IF P6>0 and Q6>0, then (Q6-P6)/P6. Any suggestions or help would be greatly appreciated. Thank you.

 

[snjpverma]

What if one is zero and second is not a zero?

Try if this works:

=IF(P6*Q6,(Q6-P6)/P6,)

 

[awoohaw]

Book1
	O	P	Q	R
5		Period1	Period2
6		0.00%	0.00%	0.00%
7		0.00%	5.00%	0.00%
8		3.00%	0.00%	-100.00%
9		3.00%	5.00%	66.67%
Sheet1

Cell Formulas
Range	Formula
R6:R9	R6	=IF(OR(AND(P6=0,Q6=0),P6=0),0,(Q6-P6)/P6)

 

[JustDebbie5]

Do you have any suggestions how if the previous quarter was zero? Perhaps populate text that says "Divide by Zero Error"? If current quarter is zero, then I would like it to calculate/populate a negative variance. Thanks for any help.

 

[awoohaw]

try this:

Book1
	O	P	Q	R
5		Period1	Period2
6		0.00%	0.00%	0.00%
7		0.00%	5.00%	DIV/0 Error
8		3.00%	0.00%	-100.00%
9		3.00%	5.00%	66.67%
Sheet1

Cell Formulas
Range	Formula
R6:R9	R6	=IF(AND(P6=0,Q6=0),0,IF(P6=0,"DIV/0 Error",(Q6-P6)/P6))

 

[JustDebbie5]

Book1
	O	P	Q	R
5		Period1	Period2
6		0.00%	0.00%	0.00%
7		0.00%	5.00%	0.00%
8		3.00%	0.00%	-100.00%
9		3.00%	5.00%	66.67%
Sheet1

Cell Formulas
Range	Formula
R6:R9	R6	=IF(OR(AND(P6=0,Q6=0),P6=0),0,(Q6-P6)/P6)

Book1
	O	P	Q	R
5		Period1	Period2
6		0.00%	0.00%	0.00%
7		0.00%	5.00%	0.00%
8		3.00%	0.00%	-100.00%
9		3.00%	5.00%	66.67%
Sheet1

Cell Formulas
Range	Formula
R6:R9	R6	=IF(OR(AND(P6=0,Q6=0),P6=0),0,(Q6-P6)/P6)

Awoohaw, your formula worked great. Thanks so much for your time and help.

 

[awoohaw]

Happy to help!

Best wishes!

 

[awoohaw]

@JustDebbie5 , my second post (Post #5) answers your division by zero question.

 

[JustDebbie5]

@JustDebbie5 , my second post (Post #5) answers your division by zero question.

Awoohaw, with your formula, I discovered that if a cell in column Q (the current quarter) is blank, the variance is still showing as 0%, which wouldn't be accurate or draw my attention to research. Is there a way that if the cell were blank, the variance would show -100% or something to bring it to my attention to review? Thanks again for all your help today.

 

[awoohaw]

Awoohaw, with your formula, I discovered that if a cell in column Q (the current quarter) is blank, the variance is still showing as 0%, which wouldn't be accurate or draw my attention to research. Is there a way that if the cell were blank, the variance would show -100% or something to bring it to my attention to review? Thanks again for all your help today.

Isn't that what is done in cell R8?
Or we could do just like when Column Q is zero... type something like "Not enough Data"?