PDA

View Full Version : Sleeper: "statistical" function to randomize..



frade
09-26-2005, 03:01 AM
Hello everybody,

I would like to distribute "equally" the number of analysis during a year.
In one of my examples, I have 30 analysis and thus 12 months.
The name of the months are already written in one column A
For each month, you can have one or two analysis indeed (column B)
How to do that automatically without taking any personnal choice?

Nevertheless automatically or semi automatically..it's not really necessary to do that with VBA..

Thanks by advance

Regards,

Fran?ois

MWE
09-26-2005, 06:36 AM
Hello everybody,

I would like to distribute "equally" the number of analysis during a year.
In one of my examples, I have 30 analysis and thus 12 months.
The name of the months are already written in one column A
For each month, you can have one or two analysis indeed (column B)
How to do that automatically without taking any personnal choice?

Nevertheless automatically or semi automatically..it's not really necessary to do that with VBA..

Thanks by advance

Regards,

Fran?ois
It is important to realize that "random" does not mean "equally distributed" over the short run. Given your situation, if we were to randomly pick a month for the list of tasks and repeat that process several hundred times, the average results would be what you want. In the short run, you could easily have an unequal distriubtion of tasks among the 12 months. For a single iteration, i.e., randomly select a month for each of the 36 tasks, I guarantee that at least one month will have two tasks and at least one month will have 4. It is likely that at least one month will have 1 task and at least one month will have 5 tasks.

Having said that, this problem can be solved either with Excel's formula capability or using VBA. VBA has the advantage of easily "cleaning up" problems that random selection will cause. For example, it is relatively easy in VBA to keep track of how many tasks have been assigned to a given month and "adjust" things near the end to achieve the truely equal result you seek.

In the attached example spreadsheet, you can comment out the line of VBA that forces 3 tasks / month and see the results.