Generate Random Number for "Work from Home"

**jp16** · 09-13-2023, 11:50 AM

Hi, not sure if this is even possible in Excel.

I have 7 staff under me and i have to plan which staff come back to office everyday. Denote by x in my attachment
Our policy is that Everyday must have at least 3 staff (can be more but cannot be less)
Each staff to be back to office at least 2 times per week. (best is 2 but if no choice, can be 3)

However, we have to change our schedule every week. Its quite troublesome for me to always plan as i always has to make sure the above is met.

Hence i am thinking if Excel is possible to generate random array base on the above requirement.

**Bernie Deitrick** · 09-13-2023, 01:34 PM

Formulas with a macro works pretty well - open the file, enable macros, and click the button.

Note that with 7 staff members and 15 slots to fill, Steve will always have 3 days in the office (since he is the only one selected to be in more than twice)

**jp16** · 09-13-2023, 01:36 PM

Originally Posted by Bernie Deitrick

Formulas with a macro works pretty well - open the file, enable macros, and click the button.

Note that with 7 staff members and 15 slots to fill, Steve will always have 3 days in the office (since he is the only one selected to be in more than twice)

Haha. Thanks! Its fun watching the x jumping here n there.

**HansDouwe** · 09-13-2023, 02:16 PM

Here is a solution with a recursive formula.

The recursive formula generates an absolutely random schedule that meets all your requirements.
The person who is assigned 3 times is also random.

Please add to the namemanager: genplan:

Please Login or Register  to view this content.

and delete all expected results and try

Formula:

Please Login or Register  to view this content.

**Bernie Deitrick** · 09-13-2023, 03:21 PM

Wow! - I got an error message about this part:

--MID(c,SEQUENCE(,7)) ,1)

I think it needs one fewer right parens:

--MID(c,SEQUENCE(,7) ,1)

then it worked wonderfully....

**HansDouwe** · 09-13-2023, 03:29 PM

@Bernie, Thanks for the feedback and pointing the error. It is a typo. I'll remove the bracket in Post #4.

In the attached file the formula is correct.

**jp16** · 09-24-2023, 01:01 PM

hey, thanks for the help. It work well. Sorry that i am not able to response in time as i am overseas.

**HansDouwe** · 09-24-2023, 05:09 PM

You are Welcome!

Thanks for the feedback and glad to have helped.

.

If you haven't already, please consider adding reputation to all helpers you think they deserve.
In that case please click * Add Reputation left below their answers.

Generate Random Number for "Work from Home"

LinkBack

Thread Tools

Rate This Thread

Display

Generate Random Number for "Work from Home"

Re: Generate Random Number for "Work from Home"

Re: Generate Random Number for "Work from Home"

Re: Generate Random Number for "Work from Home"

Re: Generate Random Number for "Work from Home"

Re: Generate Random Number for "Work from Home"

Re: Generate Random Number for "Work from Home"

Re: Generate Random Number for "Work from Home"

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Generate Random Char "x" in Range with Condition

Tried formula variations including "if", "sumif", "min", "match" but can't get it to work.

Help with "Generate Password" Button to generate random alphanumeric code and insert

When I Press Home in keyboard, sheet called "Home" should activate

Team Rotating "Work From Home" Day

[SOLVED] Get Back "#N/A" When work out "relative ratio" by PIVOT's "Show Value as /Difference from"

Trying to generate the letter "X" in random blocks

Bookmarks

Bookmarks

Posting Permissions