Distribute staff attendance evenly across the week

[DMoraes]

Hi all,
I'm new to this forum and this is my first query. I have looked for what I need in the forum and found partial responses. But nothing that met what I needed.

I have a certain number of people attending the company's office. Our office has less desks than employees. In essence, we cannot have everyone attending office on the same day. What I wanted to do in Excel was to create a model which I could use to distribute staff attendance evenly throughout the week. For example, if I had 10 staff, I would like to have not more than 3/5ths of those attending office each and every day (formula: no. of staff * 3 days a week /5 week days = 10*3/5 = 6).
Now I need to distribute those 10 staff making sure that each day of the week has no more than 6 staff attending office and that each staff cannot attend the office more than three times a week.
Doing that manually for such a small number of staff is quite simple. However, I'm talking of several offices with large numbers of staff. Doing it manually would be a nightmare as the number of staff will vary depending on people leaving and new staff being hired.
The attached file shows very simplistically what I'd like to achieve. The result needs to consider both the maximum number of staff per day and the maximum number of times each staff can attend office during the week. For example, Staff A attends office on Mon, Wed and Fri; Staff B on Mon, Tue and Wed and so on. However, on Monday, I have 6 staff in the office out of the 10. Same for each other day of the week.
Sorry if this has already been answered but I failed to find it.
And thanks in advance for your thoughts.
Cheers,
Dom

 

[DMoraes]

Hi guys, I think I've found an easier solution to this. The fact is quite simple from a mathematical perspective. It is a combination of 5 choose 3. The maximum ways that five numbers can be arranged choosing 3 at a time is 10. That means that I can arrange the attendance of 10 staff each one attending the office 3 times a week over a 5 day working week. That will guarantee that on each day only 6 staff will be in the office. All I have to do is identify the 10 different ways/patterns and then repeat them for numbers above 10 employees. So please consider this resolved.
Thanks,
Dom

 

[Logit]

Glad you have found your answer.

Please provide a sample workbook with the solution to assist others.

Thank you.

 

[DMoraes]

Hi all,
as Logit has suggested, I'm placing a copy of the spreadsheet with the 10 patterns that are available for 10 staff working 3 days a week in the office. The pattern allows each day of the week to have 6 staff attending the premises, for the first 10 staff. This solution does not provide any coding or use of formulae as it is quite simple. All that is required is repeat the pattern for numbers of staff above 10.

In other words, the 11th plus staff will have a pattern which was used before. For example, if we have 12 staff, staff 11 might have the same pattern as staff 1; staff 12 might have the same pattern as staff 10. Of course, this makes it impossible to have same number o staff attending office for every day of the week when numbers are not a multiple of 10. What the pattern allows is to minimise the difference in number of staff attending office across each day of the week (e.g one day having 10 staff and the next only 2). Please have a look at attached file for your reference.

Thanks,
Dom