title: How can I implement 'balanced' error spreading functionality in Excel?
body: <p>I have a requirement in Excel to spread small; i.e. pennies, monetry rounding errors fairly across the members of my club.</p>
  
  <p>The error arises when I deduct money from members; e.g. £30 divided between 21 members is £1.428571... requiring £1.43 to be deducted from each member, totalling £30.03, in order to hit the £30 target.</p>
  
  <p>The approach that I want to take, continuing the above example, is to deduct £1.42 from each member, totalling £29.82, and then deduct the remaining £0.18 using an error spreading technique to randomly take an extra penny from 18 of the 21 members.</p>
  
  <p>This immediately made me think of Reservoir Sampling, and I used the information here: <a href="https://stackoverflow.com/questions/5416567/random-selection/5417178#5417178">Random selection</a>,
   to construct the test Excel spreadsheet here: <a href="https://www.dropbox.com/s/snbkldt6e8qkcco/ErrorSpreading.xls" rel="nofollow noreferrer">https://www.dropbox.com/s/snbkldt6e8qkcco/ErrorSpreading.xls</a>, on Dropbox, for you guys to play with...</p>
  
  <p>The problem I have is that each row of this spreadsheet calculates the error distribution indepentently of every other row, and this causes some members to contribute more than their fair share of extra pennies.</p>
  
  <p>What I am looking for is a modification to the Resevoir Sampling technique, or another balanced / 2 dimensional error spreading methodology that I'm not aware of, that will minimise the overall error between members across many 'error spreading' rows.</p>
  
  <p>I think this is one of those challenging problems that has a huge number of other uses, so I'm hoping you geniuses have some good ideas!</p>
  
  <p>Thanks for any insight you can share :)</p>
  
  <p>Will</p>