# random number based on % needed based on a 12 month period.

• ### Question

• I have a number based on the number of employees that fluctuates throughout a 12 month calendar year.  I need to have 50% of the employee Pool selected at the end of the calendar year.  my selection is once a quarter. I have to request a current employee list every selection.  what formula can I set up to calculate properly?  of course the number of employees will fluctuate every selection that is made.

how do I make sure that at the end of the year, I have 50% of employees tested? what formula can I use for this type of random pool?

thank you very much.

Wednesday, July 27, 2011 11:13 PM

### All replies

• Hi Cmyrick1

cmyrick1 wrote:

I have a number based on the number of employees that fluctuates
throughout a 12 month calendar year. I need to have 50% of the employee
Pool selected at the end of the calendar year. my selection is once a
quarter. I have to request a current employee list every selection. what
formula can I set up to calculate properly? of course the number of
employees will fluctuate every selection that is made.

how do I make sure that at the end of the year, I have 50% of employees
tested? what formula can I use for this type of random pool?

Not sure if I understand correctly. By end of the year you need 50% of the employees tested. But you have to make the selection quarterly.

1.Quarter: 12.5% of the total employees
2.Quarter: 12.5% of the total employees
3.Quarter: 12.5% of the total employees
4.Quarter: 12.5% of the total employees

Now in the 2nd quarter only 87.5% of the employees aren't tested yet. You therefore have to test more than 12.5% of the remaining employees by calculate 12.5% / 0.875, therefore you have to test 14.29% of the remaining employees. Now at the end of the 2. Quarter remaining 75% untested employees. Now you calculate the same way again: 12.5% / 0.75 = 16.67% to be tested. At the end of the 3rd quarter 62.5% of the employees are untested. This means you have to test 12.5% / 0.625 = 20% of the remaining employees. As the number of employees may fluctuate you at the end of the 4th quarter you better take 50% of the total employees at this time, subtract the numbers of already tested employees and take this number of employees to be tested.
untested% test%   remaining%
Q1 100.0     12.50%  87.5
Q2  87.5     14.29%  75.0
Q3  75.0     16.67%  62.5
Q4  62.5     20.00%  50.0

BTW: you can use 1/8, 1/7, 1/6, 1/5 instead of the percentages.

HTH

Henry

Thursday, July 28, 2011 3:05 AM
• If 50% have to be selected by end of year, the selection in the last periods cannot be random. Maybe random for resources not yet selected.

Simplest way may be to use Rand or RandBetween functions in Excel and press F9 until you get teh result you need.

Rod Gill

The one and only Project VBA Book Rod Gill Project Management
Thursday, July 28, 2011 4:38 AM