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)
All assistance in much appreciated
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>