x&y option : My best wishes to all the poeple of U.S.A
Posted by gordon on September 17, 2001 6:20 PM
The help I need is, if x is value in cell a1. And y is a value in b1. I want to have a countdown value off of a split based on x&y based on a percent. Example...(X is at 70% Y at 30%. How do I split the workload so that the 20 clients is distributed at 70/30 with the best clients going to the best person first, then the 30% person gets the weaker, Only to the point! Of the difference (70%-40%=30% now Strong person is 30%. Then distribute the last clients evenly to both, to give the 30% weaker person a chance to improve.
Posted by Eric on September 18, 2001 7:16 AM
This idea uses the rand() function so it may not be ideal for you
First, that's a really cool idea- I like how using the % difference between the performance of the two workers changes the number of "good" clients the more favored worker gets.
My idea on how to implement this may not be useful to you however. I used the rand() function to generate results, so, in your example, there is a 70% CHANCE that any of the top 40% of the clients will be assigned to the strong worker, and the chance beyond the top 40% is 50/50.
I've checked this by increasing the client list to 1000 and the proportions of "strong" to "weak" are correct in each section of the client list. However, the Rand function recalculates each time Excel performs a task (you could switch that to manual recalculation under Tools-->Options, select "calculate" tab), so clients get reassigned. It is also likely, especially with small client lists, that there will be, on any given cycle of calculation, a bias towards one or the other worker.
Assuming your "Strong" and "Weak" worker values are in a2 and b2, and your client list in in col (C), put the following in D2 and copy down as needed:
=IF(ROW()-1<=ABS(A$2/(A$2+B$2)-B$2/(A$2+B$2))*COUNTIF(C:C,"<>0"),(IF(RAND()>MIN((A$2/(A$2+B$2)),(B$2/(A$2+B$2))),"strong","weak")),(IF(RAND()>0.5,"strong","weak")))
This one assumes that your clients in the list are sorted from best to worst, calculates the number of clients that fit in that top of the list, assigns the top group according to the "strong"-favored" percentages, and and the rest of the list according to the 50/50 protocol.
Hope that helps
