Hi... i have list of cases resolved by agents. Lets say every agent resolved more than 5 case but i want to randomly show 3 cases against each agent. Any idea how i can do that ?
Thanks for the help .I am lost in this logic Mod(row),10+1 Can you please explain . and is there any array formula for this . i have been watching mike garvin videos a lot and and he explain each logic in depth :)
Picture yourself back in school when your phys ed teacher told a line of students to count off by 4's: You would count 1, 2, 3, 4, 1, 2, 3, 4, 1, 2, 3, 4. The MOD construct is designed to repeat the numbers 1 through 10 over and over. First ROW() returns a row number. As you copy the ROW function down, it is pointing at one row lower, so the result of row increased. The MOD function (short for Modulo) divides the row number by 10 and gives you just the remainder. For example: (27/10) is 2 with a remainder of 7. =MOD(ROW(A27),10) gives you 7. For rows 10, 20, 30, the MOD function returns the number 0 (which causes you to get a series of numbers 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0. Since I want the numbers 1 through 10, I add 1 to the result of MOD.
Hi Bill, is it possible to assign task equally to employee according to country they choose but at the same time everyone need to get equal amount of task for example (1 employee get 40 task). I have 2 spreadsheet which one have 800 task with country and another 1 spreadsheet with employee detail and country they choose. First rule I assign according to country they want then I sort back and make sure they get 40 task. I already try in Excel but didn't get any solution. Do Bill help any formula able to help I'm kinda lost
Thanks Bill. What if I want to have a minimum frequency assigned to each representative. Instead of 10 each, say we have a variable number. How to handle that by formulas ?
Hi MrExcel. Good Day. I happened to see your video, looks very useful. Thank u. I need your advise on the following requirement, if possible. How to assign the members equally? For example, for one week i have 25 members, next week i may have 28 members. Every week the members count will not be constant. In such case, how can i assign them equally under 4 teams? I understand if there are 20 members, they can be assigned 5 members under each team equally. But If there are 21 members, they should be assigned as follows. Team 1 - 6 members, Team 2 - 5 members, Team 3 - 5 members, Team 4 - 5 members. Total 21 members, under 4 teams. Can this be done? please advise.
Try this: 1drv.ms/x/s!As7G72Sl487Jlg4_lzQBK2gdOObx?e=Pjkxcc As an extra bonus, this sorts the headings for Team 1, Team 2, Team 3, Team 4. That way, if there is one extra member, it won't always go to Team 1.
Thank you so much Sir. It works 90% for my requirement and hopefully i would able to complete the rest from my end. Thanks again for your guidance. Really appreciate it. Hope i can reach you for any excel help in future.
@@balajivasudevan4124 You will always get a quicker response by posting to the huge community of Excel fans at www.mrexcel.com/board/ At my site, you can post a screenshot of your data. When we can see what you have and what you are trying to do, we could get to 99.9% of your problem instead of 90%.
If "about 10" and "about 20" is okay (in other words, some days Ben gets 19, 20, or 21), you could put Ben in the list twice and use the INDEX(,RANDBETWEEN) formula. With Ben occurring twice, he will get picked on average twice as often as everyone else. If you really absolutely have to have exactly 10 for Ana and exactly 20 for Ben and exactly 17 for Cici and exactly 2 for Dee, then it becomes a little more complicated: 1. Create a range starting in H3 with 20 cells that say Ben, 10 cells that say Ana, and so on. The last cell should be H102. 2. Add headings in H2: "Name" and in I2: Random 3. The formula for I3 is =RAND() 4. Copy the formula in I3 down to I102 5. Sort by column I 6. The formula in B2 is =H3 7. Copy the B2 formula down to B101 The steps above will work in Excel 97 or newer. If you have Microsoft 365 and have the new SORTBY function, then it is slightly simpler. 1. Create a range starting in H3 with 20 cells that say Ben, 10 cells that say Ana, and so on. The last cell should be H102. 2. The single formula for B2 is =SORTBY(H3:H103,RANDARRAY(100))
Sorry, I mean how to solve the problem if we do not want to assign equally. I want some of the representative to get more share and some less based on pre defined number. So rand() will not serve the purpose as allocation will not be based on pre decided values and mod() distributes equally. Thanks.
This was really helpful but can you please do a little more explaining in what you do in future tutorials. It took me awhile to figure some steps out. it would greatly help excel dummies like me.
thanks you. But How can we equally Randomly Assign ?
Hi... i have list of cases resolved by agents. Lets say every agent resolved more than 5 case but i want to randomly show 3 cases against each agent. Any idea how i can do that ?
Thanks for the help .I am lost in this logic
Mod(row),10+1
Can you please explain . and is there any array formula for this . i have been watching mike garvin videos a lot and and he explain each logic in depth :)
Picture yourself back in school when your phys ed teacher told a line of students to count off by 4's: You would count 1, 2, 3, 4, 1, 2, 3, 4, 1, 2, 3, 4. The MOD construct is designed to repeat the numbers 1 through 10 over and over. First ROW() returns a row number. As you copy the ROW function down, it is pointing at one row lower, so the result of row increased. The MOD function (short for Modulo) divides the row number by 10 and gives you just the remainder. For example: (27/10) is 2 with a remainder of 7. =MOD(ROW(A27),10) gives you 7. For rows 10, 20, 30, the MOD function returns the number 0 (which causes you to get a series of numbers 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0. Since I want the numbers 1 through 10, I add 1 to the result of MOD.
Hi Bill, is it possible to assign task equally to employee according to country they choose but at the same time everyone need to get equal amount of task for example (1 employee get 40 task). I have 2 spreadsheet which one have 800 task with country and another 1 spreadsheet with employee detail and country they choose. First rule I assign according to country they want then I sort back and make sure they get 40 task. I already try in Excel but didn't get any solution. Do Bill help any formula able to help I'm kinda lost
What a good idea. thanks Bill
Thanks Bill. What if I want to have a minimum frequency assigned to each representative. Instead of 10 each, say we have a variable number. How to handle that by formulas ?
Hi MrExcel. Good Day. I happened to see your video, looks very useful. Thank u. I need your advise on the following requirement, if possible. How to assign the members equally? For example, for one week i have 25 members, next week i may have 28 members. Every week the members count will not be constant. In such case, how can i assign them equally under 4 teams? I understand if there are 20 members, they can be assigned 5 members under each team equally. But If there are 21 members, they should be assigned as follows. Team 1 - 6 members, Team 2 - 5 members, Team 3 - 5 members, Team 4 - 5 members. Total 21 members, under 4 teams. Can this be done? please advise.
Try this: 1drv.ms/x/s!As7G72Sl487Jlg4_lzQBK2gdOObx?e=Pjkxcc
As an extra bonus, this sorts the headings for Team 1, Team 2, Team 3, Team 4. That way, if there is one extra member, it won't always go to Team 1.
Thank you so much Sir. It works 90% for my requirement and hopefully i would able to complete the rest from my end. Thanks again for your guidance. Really appreciate it. Hope i can reach you for any excel help in future.
@@balajivasudevan4124 You will always get a quicker response by posting to the huge community of Excel fans at www.mrexcel.com/board/ At my site, you can post a screenshot of your data. When we can see what you have and what you are trying to do, we could get to 99.9% of your problem instead of 90%.
Hi, what if the list of names has assigned numbers? For example Ana should have 10 and ben should have 20?
If "about 10" and "about 20" is okay (in other words, some days Ben gets 19, 20, or 21), you could put Ben in the list twice and use the INDEX(,RANDBETWEEN) formula. With Ben occurring twice, he will get picked on average twice as often as everyone else. If you really absolutely have to have exactly 10 for Ana and exactly 20 for Ben and exactly 17 for Cici and exactly 2 for Dee, then it becomes a little more complicated:
1. Create a range starting in H3 with 20 cells that say Ben, 10 cells that say Ana, and so on. The last cell should be H102.
2. Add headings in H2: "Name" and in I2: Random
3. The formula for I3 is =RAND()
4. Copy the formula in I3 down to I102
5. Sort by column I
6. The formula in B2 is =H3
7. Copy the B2 formula down to B101
The steps above will work in Excel 97 or newer. If you have Microsoft 365 and have the new SORTBY function, then it is slightly simpler.
1. Create a range starting in H3 with 20 cells that say Ben, 10 cells that say Ana, and so on. The last cell should be H102.
2. The single formula for B2 is =SORTBY(H3:H103,RANDARRAY(100))
❤ thank you
Sorry, I mean how to solve the problem if we do not want to assign equally. I want some of the representative to get more share and some less based on pre defined number. So rand() will not serve the purpose as allocation will not be based on pre decided values and mod() distributes equally. Thanks.
Could you explain a bit more how the ROW() part works? I'm not seeing what Excel is doing.
This was really helpful but can you please do a little more explaining in what you do in future tutorials. It took me awhile to figure some steps out. it would greatly help excel dummies like me.
This is super frustrating because you’re rushing over the one thing I’m trying to watch which is the formula..
play the video at a slower speed should help