Variance % Formula Issue - Revenue and Costs

M

Mr Sloth

New Member
Joined
Nov 15, 2020
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHI
1CostsRevenue
2ActBudVarVar %ActBudVarVar %
3088100%01-1100%
410-1100%101100%
521-1-100%211100%
612150%12-1-50%
7-101100%-10-1100%
80-1-1-100%0-11100%
9-2-3-1-33%-2-3133%
10-5-23150%-5-2-3-150%
110000%0000%
Sheet1
Cell Formulas
RangeFormula
C3:C11C3=B3-A3
D3:D11,I4:I11D3=IFERROR(IF(AND(B3=0,A3=0),0,IF(B3=0,1,(C3/ABS(B3)))),0)
H3:H11H3=F3-G3
I3I3=IF(AND(F3=0,G3>0),1,IF(AND(F3>0,G3=0),-1,IF(AND(F3=0,G3=0),0,(G3-F3)/F3)))
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
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
ABCDEFGHI
1CostsRevenue
2ActBudVarVar %ActBudVarVar %
3088100%01-1-100%
410-1-100%101100%
521-1-100%211100%
612150%12-1-50%
7-101100%-10-1-100%
80-1-1-100%0-11100%
9-2-3-1-33%-2-3133%
10-5-23150%-5-2-3-150%
110000%0000%
Sheet3
Cell Formulas
RangeFormula
C3:C11C3=B3-A3
I3:I11,D3:D11D3=C3/(ABS(B3)+(B3=0))
H3:H11H3=F3-G3
 
Upvote 0
bebo021999 said:
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
ABCDEFGHI
1CostsRevenue
2ActBudVarVar %ActBudVarVar %
3088100%01-1-100%
410-1-100%101100%
521-1-100%211100%
612150%12-1-50%
7-101100%-10-1-100%
80-1-1-100%0-11100%
9-2-3-1-33%-2-3133%
10-5-23150%-5-2-3-150%
110000%0000%
Sheet3
Cell Formulas
RangeFormula
C3:C11C3=B3-A3
I3:I11,D3:D11D3=C3/(ABS(B3)+(B3=0))
H3:H11H3=F3-G3
Click to expand...
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
ABCDEFGHI
1CostsRevenue
2ActBudVarVar %ActBudVarVar %
3-606600%-50-5-500%
40-6-6-100%0-55100%
540-4-400%05-5-100%
6044100%505500%
Sheet1
Cell Formulas
RangeFormula
C3:C6C3=B3-A3
D3:D6,I3:I6D3=C3/(ABS(B3)+(B3=0))
H3:H6H3=F3-G3
 
Upvote 0
bebo021999 said:
Try again:
Book1
ABCDEFGHI
1COSTREVENUE
2ActBudVarActBudVar
3-60-6-600%-505500%
40-6-6-100%0-55100%
5404400%05-5-100%
6044100%50-5-500%
Sheet1
Cell Formulas
RangeFormula
C3:C6C3=IF(B3=0,B3+A3,B3-A3)
I3:I6,D3:D6D3=C3/(ABS(B3)+(B3=0))
H3:H6H3=IF(G3=0,G3-F3,F3-G3)
Click to expand...
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
ABCDEFGHI
1CostsRevenue
2ActBudVarVar %ActBudVarVar %
3-606100%-50-5-100%
40-6-6-100%0-55100%
540-4-100%05-5-100%
6044100%505100%
Sheet1
Cell Formulas
RangeFormula
D4,D6,I4:I5D4=C4/(ABS(B4)+(B4=0))
C3:C6C3=B3-A3
H3:H6H3=F3-G3
 
Upvote 0
Mr Sloth said:
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
ABCDEFGHI
1CostsRevenue
2ActBudVarVar %ActBudVarVar %
3-606100%-50-5-100%
40-6-6-100%0-55100%
540-4-100%05-5-100%
6044100%505100%
Sheet1
Cell Formulas
RangeFormula
D4,D6,I4:I5D4=C4/(ABS(B4)+(B4=0))
C3:C6C3=B3-A3
H3:H6H3=F3-G3
Click to expand...
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
ABCDEFGHI
1CostsRevenue
2ActBudVarVar %ActBudVarVar %
3-6060%-50-50%
40-6-60%0-550%
540-40%05-50%
60440%5050%
Sheet1
Cell Formulas
RangeFormula
C3:C6C3=B3-A3
H3:H6H3=F3-G3
 
Upvote 0
Mr Sloth said:
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
ABCDEFGHI
1CostsRevenue
2ActBudVarVar %ActBudVarVar %
3-6060%-50-50%
40-6-60%0-550%
540-40%05-50%
60440%5050%
Sheet1
Cell Formulas
RangeFormula
C3:C6C3=B3-A3
H3:H6H3=F3-G3
Click to expand...
Managed to find the formula when comparing against a comparitor of 0:

Variance ^1 Formulas.xlsx
ABCDEFGHI
1CostsRevenue
2ActBudVarVar %ActBudVarVar %
3205-15-300%20515300%
45201575%520-15-75%
5020200%020-200%
6200-200%200200%
70000%0000%
8-1010%-10-10%
90-1-10%0-110%
10-2-3-1-33%-2-3133%
11-3-2150%-3-2-1-50%
Sheet1
Cell Formulas
RangeFormula
C3:C11C3=B3-A3
D3:D11,I3:I11D3=IFERROR(IF(OR(B3=0,A3=0),0,((C3/ABS(B3)))),0)
H3:H11H3=F3-G3
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Update List Number Going Forward in a Column Once Other Cell Is Greater Than A Specific $ Number
2
Replies
13
Views
229
bear1970
B
B
  • Question Question
Suggestions for Building a Historical Cost Database by Project
Replies
0
Views
98
babyjwhale
B
K
  • Solved
Average Based on Criteria
Replies
3
Views
388
Fluff
Fluff
S
  • Question Question
Formula for Equity Vesting
Replies
6
Views
1K
Dave Patton
D
F
  • Question Question
Replace all IFERROR & INDEX & MATCH formulas with XLOOKUP
Replies
4
Views
658
Fabulist
F

Forum statistics

Threads
1,224,855
Messages
6,181,423
Members
453,039
Latest member
jr25673

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top