samyscraps01
Board Regular
- Joined
- Jul 6, 2017
- Messages
- 58
I tried the formula below
=ROUNDUP(RANK($C27,Rando)/Sizes,0) and it works great for a list of people. I need this formula to work for a group of employees rather than a list of names. My chart below shows a group of employees in each department. So 42 represents one department, 19 another group of employees in another dept, and so forth. I want the formula to group together as evenly as possible all the departments and distribute them evenly into 6 main groups to be disbursed amongst 6 leaders. This is more than just taking the total of all the employees and dividing them into six even groups. I need as many departments grouped as evenly as possible into six buckets. Is there a way to do this? Even with another formula. I tried this formula with a list of employees and it worked really well but when I applied it to the EE's in Column B I did not get the same results.
[TABLE="width: 566"]
<tbody>[TR]
[TD]Dept No.
[/TD]
[TD]EE
[/TD]
[TD]Random
[/TD]
[TD]Buckets
[/TD]
[TD]Name List
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD] 91230
[/TD]
[TD]42
[/TD]
[TD]0.583587226
[/TD]
[TD]3
[/TD]
[TD]Sizes
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]91231
[/TD]
[TD]19
[/TD]
[TD]0.829519086
[/TD]
[TD]1
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91232
[/TD]
[TD]35
[/TD]
[TD]0.393358531
[/TD]
[TD]3
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91233
[/TD]
[TD]57
[/TD]
[TD]0.775741312
[/TD]
[TD]2
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91234
[/TD]
[TD]25
[/TD]
[TD]0.141453984
[/TD]
[TD]4
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91235
[/TD]
[TD]23
[/TD]
[TD]0.023805453
[/TD]
[TD]4
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91236
[/TD]
[TD]39
[/TD]
[TD]0.511275187
[/TD]
[TD]3
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91237
[/TD]
[TD]47
[/TD]
[TD]0.212964913
[/TD]
[TD]4
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91238
[/TD]
[TD]7
[/TD]
[TD]0.23715932
[/TD]
[TD]4
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91239
[/TD]
[TD]3
[/TD]
[TD]0.774636135
[/TD]
[TD]2
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91240
[/TD]
[TD]18
[/TD]
[TD]0.52070436
[/TD]
[TD]3
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91241
[/TD]
[TD]17
[/TD]
[TD]0.300847994
[/TD]
[TD]4
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91242
[/TD]
[TD]2
[/TD]
[TD]0.841505214
[/TD]
[TD]1
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91243
[/TD]
[TD]36
[/TD]
[TD]0.789073292
[/TD]
[TD]1
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91244
[/TD]
[TD]101
[/TD]
[TD]0.825132871
[/TD]
[TD]1
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91245
[/TD]
[TD]13
[/TD]
[TD]0.886795114
[/TD]
[TD]1
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91246
[/TD]
[TD]15
[/TD]
[TD]0.704440553
[/TD]
[TD]2
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91247
[/TD]
[TD]28
[/TD]
[TD]0.733682216
[/TD]
[TD]2
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91248
[/TD]
[TD]4
[/TD]
[TD]0.280399365
[/TD]
[TD]4
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91249
[/TD]
[TD]48
[/TD]
[TD]0.326471943
[/TD]
[TD]3
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91250
[/TD]
[TD]1
[/TD]
[TD]0.576338148
[/TD]
[TD]3
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91251
[/TD]
[TD]12
[/TD]
[TD]0.695422989
[/TD]
[TD]2
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91252
[/TD]
[TD]21
[/TD]
[TD]0.676249387
[/TD]
[TD]2
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91253
[/TD]
[TD]14
[/TD]
[TD]0.857909903
[/TD]
[TD]1
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
</tbody>[/TABLE]
=ROUNDUP(RANK($C27,Rando)/Sizes,0) and it works great for a list of people. I need this formula to work for a group of employees rather than a list of names. My chart below shows a group of employees in each department. So 42 represents one department, 19 another group of employees in another dept, and so forth. I want the formula to group together as evenly as possible all the departments and distribute them evenly into 6 main groups to be disbursed amongst 6 leaders. This is more than just taking the total of all the employees and dividing them into six even groups. I need as many departments grouped as evenly as possible into six buckets. Is there a way to do this? Even with another formula. I tried this formula with a list of employees and it worked really well but when I applied it to the EE's in Column B I did not get the same results.
[TABLE="width: 566"]
<tbody>[TR]
[TD]Dept No.
[/TD]
[TD]EE
[/TD]
[TD]Random
[/TD]
[TD]Buckets
[/TD]
[TD]Name List
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD] 91230
[/TD]
[TD]42
[/TD]
[TD]0.583587226
[/TD]
[TD]3
[/TD]
[TD]Sizes
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]91231
[/TD]
[TD]19
[/TD]
[TD]0.829519086
[/TD]
[TD]1
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91232
[/TD]
[TD]35
[/TD]
[TD]0.393358531
[/TD]
[TD]3
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91233
[/TD]
[TD]57
[/TD]
[TD]0.775741312
[/TD]
[TD]2
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91234
[/TD]
[TD]25
[/TD]
[TD]0.141453984
[/TD]
[TD]4
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91235
[/TD]
[TD]23
[/TD]
[TD]0.023805453
[/TD]
[TD]4
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91236
[/TD]
[TD]39
[/TD]
[TD]0.511275187
[/TD]
[TD]3
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91237
[/TD]
[TD]47
[/TD]
[TD]0.212964913
[/TD]
[TD]4
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91238
[/TD]
[TD]7
[/TD]
[TD]0.23715932
[/TD]
[TD]4
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91239
[/TD]
[TD]3
[/TD]
[TD]0.774636135
[/TD]
[TD]2
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91240
[/TD]
[TD]18
[/TD]
[TD]0.52070436
[/TD]
[TD]3
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91241
[/TD]
[TD]17
[/TD]
[TD]0.300847994
[/TD]
[TD]4
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91242
[/TD]
[TD]2
[/TD]
[TD]0.841505214
[/TD]
[TD]1
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91243
[/TD]
[TD]36
[/TD]
[TD]0.789073292
[/TD]
[TD]1
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91244
[/TD]
[TD]101
[/TD]
[TD]0.825132871
[/TD]
[TD]1
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91245
[/TD]
[TD]13
[/TD]
[TD]0.886795114
[/TD]
[TD]1
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91246
[/TD]
[TD]15
[/TD]
[TD]0.704440553
[/TD]
[TD]2
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91247
[/TD]
[TD]28
[/TD]
[TD]0.733682216
[/TD]
[TD]2
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91248
[/TD]
[TD]4
[/TD]
[TD]0.280399365
[/TD]
[TD]4
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91249
[/TD]
[TD]48
[/TD]
[TD]0.326471943
[/TD]
[TD]3
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91250
[/TD]
[TD]1
[/TD]
[TD]0.576338148
[/TD]
[TD]3
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91251
[/TD]
[TD]12
[/TD]
[TD]0.695422989
[/TD]
[TD]2
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91252
[/TD]
[TD]21
[/TD]
[TD]0.676249387
[/TD]
[TD]2
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
[TR]
[TD]91253
[/TD]
[TD]14
[/TD]
[TD]0.857909903
[/TD]
[TD]1
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
</tbody>[/TABLE]