My employer uses a pool method of redistributing excess medical benefit money (from employees whose premium amounts are lower than the provided benefit amount to employees that have premium costs higher than the provided benefit amount). The process for distributing the excess money is that all the extra is added together then evenly split between each employee based on FTE (full time equivalent). So a half time employee would only get half of the extra amount a full time employee would get. However, when calculating this average amount per FTE, it is sometimes more than the deficit an employee has. Thus the leftovers get recollected and then redistributed amongst any employees who did not receive enough during the first round to reduce their out of pocket premium costs to zero. The problem with the current process is that each round is done via a formula that builds off the previous formula and the number of rounds (and therefore columns) needed to complete the process changes year over year. (The example has 3 rounds, but the actual sheet currently has 6, and it's looking like another at least one round/column needs to be added this year.) Is there a way to write a Google Sheets formula that recursively applies the system so that only one column is needed? The sample sheet is linked below. Would like to create a single formula that combines the work of columns G through I, and more if necessary, without using helper columns.
https://docs.google.com/spreadsheets/d/1Cld30hVUZcOpEpEEmfWR7gyGT66aOprFbhdDBGexUdw/edit?pli=1#gid=0