I'm hoping to create an automated list of required transactions based on numerical values in a google sheet. The existing data is in the form:
A1: Name B1: Bal $ C1: Share % A3: Bob B3: 5795.35 C3: 11.4 A4: Fred B4: 2236.82 C4: 17.29 A5: Lisa B5: 982.12 C5: 40.36 A6: Ian B6: 12532.00 C6: 9.85 ~~~~~~~~~~~~~~~etc~~~~~~~~~~~~~~~~~~ An: Dave Bn: 1097.50 Cn: 21.1
Where Bal is the current funds held by each person and Share is the ideal balance as a percentage of the total of those funds. I'd like to create an optimal list of required payments (rounded to the nearest $10) made between these people to bring everybody's balance close to the specified percentage of funds.
I have a satisfactory algorithm, it's just the implementation that's beyond me (I have no prior experience of google sheets and have inherited the sheet as is). Using the above data as an example, I would sort the people by (rounded) excess funds held, ie
Ian +10300 Bob +3210 Fred -1680 Dave -3680 Lisa -8160
Then keep repeating first pays last the lesser of the first's excess or the last's shortfall, and removing the person who's new excess is 0 from the list, until only one person remains on the list. So producing the following list of transactions:
E1: From F1: To G1: Amount $ E3: Ian F3: Lisa G3: 8160 E4: Bob F4: Dave G4: 3210 E5: Ian F5: Fred G5: 1680 E6: Ian F6: Dave G6: 460
The original balances don't need updating, it's simply the advised transaction list that's required. And the whole functionality is only required on demand, say on the click of a button, rather than in real time as balances are changed. Ideally I'm hoping that a clever combination of existing google spreadsheet functions would solve this, though I'm expecting it'll need a custom function. If all else fails, the easiest method for me to get to grips with would probably be a RPC to my server, though that still leaves me figuring out how to build the outgoing query from a spreadsheet and handle the return data. I really am a beginner with these things and would appreciate any solutions or even pointers.