Equally distribution of works to members

[MSKUMAR]

Hi Team,

This forum really helps me a lot when ever i am trying to find some new things about excel.

I just tried building formula for equally distributing work to team members.

But it is not distributing equally. Please help me for this issue.

Thanks
Sunil

 

[Hui]

Sunil

Firstly, Welcome to the Chandoo.org Forums

Your sample size is too small to get meaningful results

You have chosen 40 CRI's, which means on average there should be 13 of Work A, B & C
13 CRI's split between 3 staff is 4 each

This is on average

So to get results like 5,2,5 for each staff is quite expected

If you extend the Number of CRI's to 2000
and split the work between 3 staff I get results like 230,190,240
They are rarely even as in 222,222,222 which they should be for an even split

You may be better off scheduling staff rather than randomly selecting them for the CRI's
ie: Setup a roster of Staff 1, 2 3 then repeat

 

[MSKUMAR]

Hi Hui

Thanks for your reply.

As you advised, I made my sample size to 2000 and I did not repeat users for my better understanding so that i can check each user getting equally or not.

I am sorry to say you that i am not sure which formula need to be used for equal split to members rather than random split. Please guide me which formula i need be used.

May i know what is
"off scheduling staff rather than randomly selecting them for the CRI's
ie: Setup a roster of Staff 1, 2 3 then repeat"

Thanks
Sunil

 

[AVK]

I have gone through your file. I have some doubt, plz clarify below point.
What is criteria about distributions of work?. i.e.
1) Work A distribute within work A team member only or with all group team members.
2) Work B distribute within work B team member only or with all group team members.
3) Work C distribute within work C team member only. or with all group team members.
Secondly, in earlier file, i found 2 names is duplicate in your team member. (sunil & phani)
Can work A or B or C group member enter into another work group?
As per my assumption, i have create one file. Refer attachment & confirm.

 

[Hui]

Instead of trying to randomly choose people for the jobs,
Assign them in order
ie: Person A then B the C then repeat
That way you are guaranteed to get an even distribution

 

[MSKUMAR]

Hi Atul,

Thanks for reply

sorry for delayed reply, I am on leave for some days.

I will confirm that Work A,B,C members can enter into any group since members should not be free if they are not having work in particular group.

I tried with out using name manager. it is giving an error.

Thanks
Sunil

 

[MSKUMAR]

Hi Atul,

Can you please help?

Thanks
Sunil

 

[Jake Collins]

Dear MSKUMAR

this formula in F2 and dragged down will do what you want.

=IF(F2="Work A",INDEX($A$2:$A$14,MOD(COUNTIF($F$2:$F2,"Work A"),COUNTA($A$2:$A$14))+1,1),IF(F2="Work B",INDEX($B$2:$B$14,MOD(COUNTIF($F$2:$F2,"Work B"),COUNTA($B$2:$B$14))+1,1),IF(F2="Work C",INDEX($C$2:$C$14,MOD(COUNTIF($F$2:$F2,"Work c"),COUNTA($C$2:$C$14))+1,1),"")))

 

[MSKUMAR]

Thanks a lot Jake. Thank you so much.