Variance % Formula Issue - Revenue and Costs

[Mr Sloth]

Hi All,

Please see spreadsheet attached.

I have an issue where I can't get the variance percentage for revenue and costs correct for every scenario. It covers most scenarios but there is some scenarios where it calculates it wrong.

All of the green highlighted cells are showing the correct variance %. But the cells in red are showing the incorrect variance %. In Cell D4 for costs it is showing +100% instead of -100% and in Cell I7 it is showing +100% instead of -100%.

Any help would be greatly appreciated!

Thanks.

Variance ^1 Formulas.xlsx
	A	B	C	D	E	F	G	H	I
1	Costs					Revenue
2	Act	Bud	Var	Var %		Act	Bud	Var	Var %
3	0	8	8	100%		0	1	-1	100%
4	1	0	-1	100%		1	0	1	100%
5	2	1	-1	-100%		2	1	1	100%
6	1	2	1	50%		1	2	-1	-50%
7	-1	0	1	100%		-1	0	-1	100%
8	0	-1	-1	-100%		0	-1	1	100%
9	-2	-3	-1	-33%		-2	-3	1	33%
10	-5	-2	3	150%		-5	-2	-3	-150%
11	0	0	0	0%		0	0	0	0%
Sheet1

Cell Formulas
Range	Formula
C3:C11	C3	=B3-A3
D3:D11,I4:I11	D3	=IFERROR(IF(AND(B3=0,A3=0),0,IF(B3=0,1,(C3/ABS(B3)))),0)
H3:H11	H3	=F3-G3
I3	I3	=IF(AND(F3=0,G3>0),1,IF(AND(F3>0,G3=0),-1,IF(AND(F3=0,G3=0),0,(G3-F3)/F3)))

 

[bebo021999]

You are using 2 different formula, one for I3 and one for I4 down.
My formula in I3 down is exactly what you expect, but I3=-100% (you expect it +100%, why, while Act revenue is 0, vs Bud is 1)

Book1
	A	B	C	D	E	F	G	H	I
1	Costs					Revenue
2	Act	Bud	Var	Var %		Act	Bud	Var	Var %
3	0	8	8	100%		0	1	-1	-100%
4	1	0	-1	-100%		1	0	1	100%
5	2	1	-1	-100%		2	1	1	100%
6	1	2	1	50%		1	2	-1	-50%
7	-1	0	1	100%		-1	0	-1	-100%
8	0	-1	-1	-100%		0	-1	1	100%
9	-2	-3	-1	-33%		-2	-3	1	33%
10	-5	-2	3	150%		-5	-2	-3	-150%
11	0	0	0	0%		0	0	0	0%
Sheet3

Cell Formulas
Range	Formula
C3:C11	C3	=B3-A3
I3:I11,D3:D11	D3	=C3/(ABS(B3)+(B3=0))
H3:H11	H3	=F3-G3

 

[Mr Sloth]

You are using 2 different formula, one for I3 and one for I4 down.
My formula in I3 down is exactly what you expect, but I3=-100% (you expect it +100%, why, while Act revenue is 0, vs Bud is 1)

Book1
	A	B	C	D	E	F	G	H	I
1	Costs					Revenue
2	Act	Bud	Var	Var %		Act	Bud	Var	Var %
3	0	8	8	100%		0	1	-1	-100%
4	1	0	-1	-100%		1	0	1	100%
5	2	1	-1	-100%		2	1	1	100%
6	1	2	1	50%		1	2	-1	-50%
7	-1	0	1	100%		-1	0	-1	-100%
8	0	-1	-1	-100%		0	-1	1	100%
9	-2	-3	-1	-33%		-2	-3	1	33%
10	-5	-2	3	150%		-5	-2	-3	-150%
11	0	0	0	0%		0	0	0	0%
Sheet3

Cell Formulas
Range	Formula
C3:C11	C3	=B3-A3
I3:I11,D3:D11	D3	=C3/(ABS(B3)+(B3=0))
H3:H11	H3	=F3-G3

Thank you very much for your reply. Not sure why I had two separate formulas and you are correct that one of the ones I highlighted green I shouldn't have.

Your formula is nearly perfect for what I want. Is there a way that I can change the below red highlighted cells to +100% or -100% when comparing an actual figure against a 0 budget? Please see below:

Variance ^1 Formulas.xlsx
	A	B	C	D	E	F	G	H	I
1	Costs					Revenue
2	Act	Bud	Var	Var %		Act	Bud	Var	Var %
3	-6	0	6	600%		-5	0	-5	-500%
4	0	-6	-6	-100%		0	-5	5	100%
5	4	0	-4	-400%		0	5	-5	-100%
6	0	4	4	100%		5	0	5	500%
Sheet1

Cell Formulas
Range	Formula
C3:C6	C3	=B3-A3
D3:D6,I3:I6	D3	=C3/(ABS(B3)+(B3=0))
H3:H6	H3	=F3-G3

 

[bebo021999]

Try again:

Book1
	A	B	C	D	E	F	G	H	I
1		COST					REVENUE
2	Act	Bud	Var			Act	Bud	Var
3	-6	0	-6	-600%		-5	0	5	500%
4	0	-6	-6	-100%		0	-5	5	100%
5	4	0	4	400%		0	5	-5	-100%
6	0	4	4	100%		5	0	-5	-500%
Sheet1

Cell Formulas
Range	Formula
C3:C6	C3	=IF(B3=0,B3+A3,B3-A3)
I3:I6,D3:D6	D3	=C3/(ABS(B3)+(B3=0))
H3:H6	H3	=IF(G3=0,G3-F3,F3-G3)

 

[Mr Sloth]

Try again:

Book1
	A	B	C	D	E	F	G	H	I
1		COST					REVENUE
2	Act	Bud	Var			Act	Bud	Var
3	-6	0	-6	-600%		-5	0	5	500%
4	0	-6	-6	-100%		0	-5	5	100%
5	4	0	4	400%		0	5	-5	-100%
6	0	4	4	100%		5	0	-5	-500%
Sheet1

Cell Formulas
Range	Formula
C3:C6	C3	=IF(B3=0,B3+A3,B3-A3)
I3:I6,D3:D6	D3	=C3/(ABS(B3)+(B3=0))
H3:H6	H3	=IF(G3=0,G3-F3,F3-G3)

Thanks for reply. Sorry I probably didn't explain well, what I meant was - when I am comparing against a 0 number I want it to just say either -100% or +100%. Please see below how I would like it to look whilst also combing the initial formula you gave me:

Variance ^1 Formulas.xlsx
	A	B	C	D	E	F	G	H	I
1	Costs					Revenue
2	Act	Bud	Var	Var %		Act	Bud	Var	Var %
3	-6	0	6	100%		-5	0	-5	-100%
4	0	-6	-6	-100%		0	-5	5	100%
5	4	0	-4	-100%		0	5	-5	-100%
6	0	4	4	100%		5	0	5	100%
Sheet1

Cell Formulas
Range	Formula
D4,D6,I4:I5	D4	=C4/(ABS(B4)+(B4=0))
C3:C6	C3	=B3-A3
H3:H6	H3	=F3-G3

 

[Mr Sloth]

Thanks for reply. Sorry I probably didn't explain well, what I meant was - when I am comparing against a 0 number I want it to just say either -100% or +100%. Please see below how I would like it to look whilst also combing the initial formula you gave me:

Variance ^1 Formulas.xlsx
	A	B	C	D	E	F	G	H	I
1	Costs					Revenue
2	Act	Bud	Var	Var %		Act	Bud	Var	Var %
3	-6	0	6	100%		-5	0	-5	-100%
4	0	-6	-6	-100%		0	-5	5	100%
5	4	0	-4	-100%		0	5	-5	-100%
6	0	4	4	100%		5	0	5	100%
Sheet1

Cell Formulas
Range	Formula
D4,D6,I4:I5	D4	=C4/(ABS(B4)+(B4=0))
C3:C6	C3	=B3-A3
H3:H6	H3	=F3-G3

Also is there a way of adapting the initial formula so that the percentage is always 0 when comparing against a comparitor of 0? Please see below:

Variance ^1 Formulas.xlsx
	A	B	C	D	E	F	G	H	I
1	Costs					Revenue
2	Act	Bud	Var	Var %		Act	Bud	Var	Var %
3	-6	0	6	0%		-5	0	-5	0%
4	0	-6	-6	0%		0	-5	5	0%
5	4	0	-4	0%		0	5	-5	0%
6	0	4	4	0%		5	0	5	0%
Sheet1

Cell Formulas
Range	Formula
C3:C6	C3	=B3-A3
H3:H6	H3	=F3-G3

 

[Mr Sloth]

Also is there a way of adapting the initial formula so that the percentage is always 0 when comparing against a comparitor of 0? Please see below:

Variance ^1 Formulas.xlsx
	A	B	C	D	E	F	G	H	I
1	Costs					Revenue
2	Act	Bud	Var	Var %		Act	Bud	Var	Var %
3	-6	0	6	0%		-5	0	-5	0%
4	0	-6	-6	0%		0	-5	5	0%
5	4	0	-4	0%		0	5	-5	0%
6	0	4	4	0%		5	0	5	0%
Sheet1

Cell Formulas
Range	Formula
C3:C6	C3	=B3-A3
H3:H6	H3	=F3-G3

Managed to find the formula when comparing against a comparitor of 0:

Variance ^1 Formulas.xlsx
	A	B	C	D	E	F	G	H	I
1	Costs					Revenue
2	Act	Bud	Var	Var %		Act	Bud	Var	Var %
3	20	5	-15	-300%		20	5	15	300%
4	5	20	15	75%		5	20	-15	-75%
5	0	20	20	0%		0	20	-20	0%
6	20	0	-20	0%		20	0	20	0%
7	0	0	0	0%		0	0	0	0%
8	-1	0	1	0%		-1	0	-1	0%
9	0	-1	-1	0%		0	-1	1	0%
10	-2	-3	-1	-33%		-2	-3	1	33%
11	-3	-2	1	50%		-3	-2	-1	-50%
Sheet1

Cell Formulas
Range	Formula
C3:C11	C3	=B3-A3
D3:D11,I3:I11	D3	=IFERROR(IF(OR(B3=0,A3=0),0,((C3/ABS(B3)))),0)
H3:H11	H3	=F3-G3