Distribute variance

[phoeberunner]

Hi All,

I would like to use Excel to do some computation. However, I have difficulty to find out a solution for the problem below.

I have a table below,

<table border="0" cellpadding="0" cellspacing="0" height="190" width="531"><tbody><tr style="min-height: 15pt;" height="20"><td style="min-height: 15pt; width: 128pt;" height="20" width="170"> <table border="0" cellpadding="0" cellspacing="0" width="541"><col style="width: 128pt;" width="170"> <col style="width: 45pt;" span="5" width="60"> <col style="width: 53pt;" width="71"> <tbody><tr style="min-height: 15pt;" height="20"> <td style="min-height: 15pt; width: 128pt;" height="20" width="170">
</td> <td style="width: 45pt;" align="right" width="60">2009</td> <td style="width: 45pt;" align="right" width="60">2010</td> <td style="width: 45pt;" align="right" width="60">2011</td> <td style="width: 45pt;" align="right" width="60">2012</td> <td style="width: 45pt;" align="right" width="60">2013</td> <td style="width: 53pt;" align="right" width="71">2014</td> </tr> <tr style="min-height: 15pt;" height="20"> <td style="min-height: 15pt;" height="20">Albert</td> <td> 77.28 </td> <td> 77.77 </td> <td> 77.83 </td> <td> 78.11 </td> <td> 78.14 </td> <td> 78.45 </td> </tr> <tr style="min-height: 15pt;" height="20"> <td style="min-height: 15pt;" height="20">Edmund
</td> <td> 87.20 </td> <td> 88.00 </td> <td> 88.43 </td> <td> 89.05 </td> <td> 89.07 </td> <td> 91.06 </td> </tr> <tr style="min-height: 15pt;" height="20"> <td style="min-height: 15pt;" height="20">Sandy
</td> <td> 92.39 </td> <td> 92.68 </td> <td> 93.68 </td> <td> 93.92 </td> <td> 96.25 </td> <td> 96.49 </td> </tr> <tr style="min-height: 15pt;" height="20"> <td style="min-height: 15pt; color: rgb(0, 0, 0); background-color: rgb(51, 102, 255);" height="20">David
</td> <td style="color: rgb(0, 0, 0); background-color: rgb(51, 102, 255);"> 340.60 </td> <td style="color: rgb(0, 0, 0); background-color: rgb(51, 102, 255);"> 343.01 </td> <td style="color: rgb(0, 0, 0); background-color: rgb(51, 102, 255);"> 344.11 </td> <td style="color: rgb(0, 0, 0); background-color: rgb(51, 102, 255);"> 345.75 </td> <td style="color: rgb(0, 0, 0); background-color: rgb(51, 102, 255);"> 348.38 </td> <td style="color: rgb(0, 0, 0); background-color: rgb(51, 102, 255);"> 351.81 </td> </tr> <tr style="min-height: 15pt;" height="20"> <td style="min-height: 15pt;" height="20">Kean
</td> <td> 115.20 </td> <td> 115.57 </td> <td> 116.45 </td> <td> 117.18 </td> <td> 117.41 </td> <td> 118.46 </td> </tr> <tr style="min-height: 15pt;" height="20"> <td style="min-height: 15pt;" height="20">Janet
</td> <td> 128.76 </td> <td> 128.93 </td> <td> 130.28 </td> <td> 132.09 </td> <td> 132.68 </td> <td> 133.00 </td> </tr> <tr style="min-height: 15pt;" height="20"> <td style="min-height: 15pt;" height="20">Lily</td> <td> 133.92 </td> <td> 135.50 </td> <td> 135.60 </td> <td> 137.10 </td> <td> 137.25 </td> <td> 137.80 </td> </tr> <tr style="min-height: 15pt;" height="20"> <td style="min-height: 15pt;" height="20"> Total</td> <td> 975.35 </td> <td> 981.46 </td> <td> 986.38 </td> <td> 993.20 </td> <td> 999.18 </td> <td> 1,007.07 </td> </tr> </tbody></table></td><td style="width: 45pt;" align="right" width="60">
</td><td style="width: 45pt;" align="right" width="60">
</td><td style="width: 45pt;" align="right" width="60">
</td><td style="width: 45pt;" align="right" width="60">
</td><td style="width: 45pt;" align="right" width="60">
</td><td style="width: 53pt;" align="right" width="71">
</td> </tr><tr style="min-height: 15pt;" height="20"><td style="min-height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>Above table shows the total sales made by each sale person from year 2009-2014. A user can change the values in the table, let say, the total sales for David from 2009 to 2014 would like to be changed to values as below,

<table border="0" cellpadding="0" cellspacing="0" width="538"><col width="160"><col span="5" width="61"><col width="73"><tbody><tr height="20"> <td style="min-height: 15pt; width: 120pt;" height="20" width="160">David
</td> <td style="width: 46pt;" width="61"> 408.72 </td> <td style="width: 46pt;" width="61"> 411.61 </td> <td style="width: 46pt;" width="61"> 412.93 </td> <td style="width: 46pt;" width="61"> 414.90 </td> <td style="width: 46pt;" width="61"> 418.05 </td> <td style="width: 55pt;" width="73"> 422.17 </td> </tr></tbody></table>
The total sales number should remain unchanged upon the changes made on David. The variance has to be absorbed proportionately by other sales person. Does anyone know a method to distribute the variance to others?


Here was my first thought for the solution,

For year 2009, the sales for David is changed to 408.72 from 340.60, so the difference is (408.72 -340.60) = 68.12. I'm thinking to divide the difference 68.12 by 6, which is 11.353. The sales for other persons will be subtracted by 11.353.

<table style="width: 279px; height: 180px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 128pt;" width="170"> <col style="width: 45pt;" width="60"> <tbody><tr style="min-height: 15pt;" height="20"> <td style="min-height: 15pt; width: 128pt;" height="20" width="170">
</td> <td style="width: 45pt;" align="right" width="60">2009</td> </tr> <tr style="min-height: 15pt;" height="20"> <td style="min-height: 15pt;" height="20">Albert</td> <td align="right">65.9243
</td> </tr> <tr style="min-height: 15pt;" height="20"> <td style="min-height: 15pt;" height="20">Edmmund</td> <td align="right">75.8446</td> </tr> <tr style="min-height: 15pt;" height="20"> <td style="min-height: 15pt;" height="20">Sandy</td> <td align="right">81.0398</td> </tr> <tr style="min-height: 15pt;" height="20"> <td style="min-height: 15pt; background-color: rgb(255, 255, 0);" height="20">David</td> <td style="background-color: rgb(255, 255, 0);" align="right">408.724</td> </tr> <tr style="min-height: 15pt;" height="20"> <td style="min-height: 15pt;" height="20">Kean</td> <td align="right">103.848</td> </tr> <tr style="min-height: 15pt;" height="20"> <td style="min-height: 15pt;" height="20">Janet</td> <td align="right">117.405</td> </tr> <tr style="min-height: 15pt;" height="20"> <td style="min-height: 15pt;" height="20">Lily</td> <td align="right">122.566</td> </tr> <tr style="min-height: 15pt;" height="20"> <td style="min-height: 15pt;" height="20">total</td> <td align="right">975.352</td> </tr> </tbody></table>

However, I've found out this doesn't work for case like a person sales is not sufficient for the subtraction. Let say if a person's total sales is 10++, then 10++ minus 11.353 would be a negative value. This is just an example, which may not be make sense to the case above.

Does anyone have a better solution to distribute the variance, may be by weight or something?


Your reply is very much appreciated.

Thanks!

 

[phoeberunner]

I've got the solution. Thanks all!