Hi Friends,

Need help with data allocation. I have a below case, excel formulas/VBA code is fine.

The case is work allocation between different employees on the basis of % and their treatment when someone is absent for any given day.




Employee 1 Employee 2 Employee 3 Employee 4 Employee 5 Employee 6 Employee 7 Total
75% 75% 75% 75% 15% 15% 10%
Tasks Total time
Task 1 10 1.88 1.88 1.88 1.88 0.75 0.75 1.00 10.00
Task 2 15 2.81 2.81 2.81 2.81 1.13 1.13 1.50 15.00
Task 3 20 3.75 3.75 3.75 3.75 1.50 1.50 2.00 20.00


Explanation of the above table
3 tasks are dividend between the employees in the above said %'s

75% of the work gets allocated between Employee 1 to 4, equally
15% of the work gets allocated between Employee 5 to 6, equally
10% of the work gets allocated between Employee 7


Conditions/Query
1) If any employee from 1 to 4 is absent, his work gets allocated to the rest (between Employee 1-4).
For eg: for Task 3, if Emp.2 is absent for any given day, his work = 3.75, gets distributed to the Emp 1, Emp 3 and Emp 4 as below
Emp 1 = Existing work + distribution from the absent employee
ie 3.75 + (3.75/3 = 1.25) = 5

2) If out of Emp 5 and 6, any one is absent, then his work goes to the other entirely.
for Eg: for Task 3, If Emp 6 is absent on a given day, his work gets allocated to Emp 5
Emp 5 = Existing work + allocation from the absent employee
ie 1.50 + 1.50 (from absent) = 3


I tried using data table, what if analysis but due to some of its limitations, could reach to the output. trying hard on this since a couple of weeks.

Can someone pls help me


Thanks
PratikAllocation template.xlsx