Need a mastermind to solve this

[pratik357]

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.

Allocation template.xlsx


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
Pratik

[skywriter]

This has a different title, but it's a duplicate thread.

[pratik357]

Yes, agree thought of posting in Excel formulas as well.

[skywriter]

When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

(link above in the menu bar)

[FDibbins]

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

[FDibbins]

Actually...
Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.
http://www.excelforum.com/excel-form...ml#post4260866