Variance Percentage Formula using 0 values

bluelabel

Board Regular
Joined
Nov 27, 2008
Messages
76
Hi Board,

I have tried searching the googlewebs and your board and there isn't much that I can find that helps me with this problem I am having.

So what I have is a standard budget with a variance.

COL A = Budget
COL B = Actual
COL C = $Variance
COL D = %Variance

Now my issue comes in where I have $0.00 in either COL A or B. I can get the formula to work either column has a $0.00 value in it but not both, I either Div/0 error out or the calculations are incorrect.

This is what I currently have. (N.B the actual data comes in off another part of the spread sheet where some of these values may = $0.00)

Code:
                                                                                                 <table border="0" cellpadding="0" cellspacing="0" width="914"><col style="width: 140pt;" width="187"> <col style="width: 184pt;" width="245"> <col style="width: 96pt;" width="128"> <col style="width: 265pt;" width="354"> <tbody><tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt; width: 140pt;" height="19" width="187">Budget</td> <td class="xl65" style="width: 184pt;" width="245">Actual</td> <td class="xl65" style="width: 96pt;" width="128">Variance</td> <td class="xl65" style="width: 265pt;" width="354">%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">=SUM('29-05-2011'!D26)</td> <td class="xl68">=SUM(C5)</td> <td class="xl67">=SUM(C5-D5)</td> <td class="xl66">=IF(ISERROR(SUM(E5/D5)),"0.00%", SUM(E5/D5))</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">=SUM(M47)</td> <td class="xl67">=SUMIF($K$7:$K$11, B8, $N$7:$N$11)</td> <td class="xl67">=SUM(D8-C8)</td> <td class="xl66">=IF(ISERROR(SUM(E8/D8)),"0.00%", SUM(E8/D8))</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">4000</td> <td class="xl67">=SUMIF($K$7:$K$11, B9, $N$7:$N$11)</td> <td class="xl67">=SUM(C9-D9)</td> <td class="xl66">=IF(ISERROR(SUM(E9/D9)),"0.00%", SUM(E9/D9))</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">0</td> <td class="xl67">=SUMIF($K$7:$K$11, B10, $N$7:$N$11)</td> <td class="xl67">=SUM(C10-D10)</td> <td class="xl66">=IF(ISERROR(SUM(E10/D10)),"0.00%", SUM(E10/D10))</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">0</td> <td class="xl67">=SUMIF($K$7:$K$11, B11, $N$7:$N$11)</td> <td class="xl67">=SUM(C11-D11)</td> <td class="xl66">=IF(ISERROR(SUM(E11/D11)),"0.00%", SUM(E11/D11))</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">=SUM(C8:C11)</td> <td class="xl67">=SUM(D8:D11)</td> <td class="xl67">=SUM(E8:E11)</td> <td class="xl66">=IF(ISERROR(SUM(E12/D12)),"0.00%", SUM(E12/D12))</td> </tr> </tbody></table>
All assistance in much appreciated
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hi

You are almost there - the SUM parts of the equations are not needed.

Per your example, assuming the actual is in column C and the budget is in column D, the variance $ formula should be:
=D8-C8

and the variance % formula should be (assuming the variance $ is in column E per your example):
=if(D8=0,0,E8/D8)

This simplistic formula doesn't take account of the actual and budget values having different signs. If that is the case then you require a different formula.

Andrew
 
Upvote 0

Forum statistics

Threads
1,224,561
Messages
6,179,521
Members
452,923
Latest member
JackiG

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