R help - Jun 2011 - distribute variance

Hi All,

I would like to use R to do some computation. However, before I proceed to
write an R script, I have difficulty to find out a solution for the question
below. Please note that the question below is not relating to question in R.

I have a table below,


2009 2010 2011 2012 2013 2014  Albert       77.28       77.77       77.83
78.11       78.14           78.45  Edmund
      87.20       88.00       88.43       89.05       89.07           91.06
Sandy
      92.39       92.68       93.68       93.92       96.25           96.49
David
    340.60     343.01     344.11     345.75     348.38         351.81  Kean
    115.20     115.57     116.45     117.18     117.41         118.46  Janet
    128.76     128.93     130.28     132.09     132.68         133.00  Lily
133.92     135.50     135.60     137.10     137.25         137.80   Total
975.35     981.46     986.38     993.20     999.18     1,007.07


























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 Simon from 2009 to 2014 would like to be change to values as
below,

  David
    408.72     411.61     412.93     414.90     418.05         422.17
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 11.353.


2009  Albert 65.9243  Edmmund 75.8446  Sandy 81.0398  David 408.724  Kean
103.848  Janet 117.405  Lily 122.566  total 975.352

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?


You help is very much appreciated. Again, I would like to emphasize that my
question above is not related to R code, I just want to find out a
methodology to solve the problem above.

Thanks!

	[[alternative HTML version deleted]]