Recently, Mr Excel provided a solution for Dean Pelton who wants to learn Excel.
His project:

With a list of cafeteria specials,
how can we randomize the meal schedule?

I like Mr. Excel’s solution, I learned a couple of new things and was intrigued by this project. Evidently, Kevin Lehrbass at MySpreadsheetLab was also inspired because he added his spin.

Mr. Excel uses the RANDBETWEEN function to get the list randomized. Kevin goes beyond basic randomization and adds weighted probabilities to the specials. I think Kevin is headed where I’m headed: a suggestion that Dean Pelton has to think this through a little further. Randomization is just the start because questions come up. Here’s just one:

Question: What if you want to serve less pizza and more veggie chili?
One Solution: Weighted probabilities will cause the randomization process to lean in favor of veggie chili.

Let’s look at the solutions provided by Mr. Excel and Kevin Lehrbass:

MR. EXCEL: The RANDBETWEEN function

Random Cafeteria Menu – Podcast #1852

KEVIN LEHRBASS: Weighted Probabilities

THE CAFETERIA MEAL RANDOMIZING MINI-APP

I started thinking … a lot. My purpose for sharing this is to go through the process of building a mini app by weaving together multiple Excel features. Yet, as Kevin says “without getting too complicated.” We could put 5 developers on this for 5 days and come out with something amazing. Amazing and too expensive. So, let’s play with this.

If I was Dean Pelton, what would be some concerns?

The 22 Cafeteria Specials, By Category

• How can I easily add new items?
• What if the randomizer puts the same special within 5 days? Example:
  • Monday: Orange duck
  • Tuesday: Fried cod
  • Wednesday: Meatloaf
  • Thursday: Fried cod (again)
  • Friday: Tacos
• There are some items that people will eat but students won’t tolerate too much over an extended period of time. Example: Peanutbutter & Jelly. More than twice in a month, the students may see the cafeteria people as lazy or cheap.
• There are complex dishes that we cannot make too frequently. Example: Lasagna
• Uncomplicated Solution! With the totality of the job, focusing on a randomizer is not worth hours or hundreds of dollars to build a perfect tool. We’re after “good enough.”

Let’s look at my solution

• Place the entrees in a table.
• Any formulas that are referring to the meals should refer to the table, and not the cells.
• Write a formula to place an X next to an entry if it has shown up in the previous 5 days.
• Rather than eyeballing to see if haggis, lasagna or PB&J are being served too much, use conditional formatting to highlight those entries.

The Cafeteria Randomizer

Why 3 Scenarios? get Excel to do more work for us. Rather than 1 randomized list that might be terrible, we get 3 and increase the likelihood of getting 1 that works. If all 3 are terrible, have Excel recalculate and give you 3 more choices. To recalculate, depending on your system, press F9 or ALT+CTRL+F9.

In the image above, scenario C looks best because it may be acceptable as-is. There are more problems in the other 2 scenarios.

Scenario A:

• Lasagna twice AND just 2 days apart.
• 4 meals are repeated within 5 days.

Scenario B:

• Pho 2 days in a row.
• Broiled Chicken 2 days in a row.
• Fried cod twice within 5 days.

Scenario C:

• PB&J is listed the first and last days of the period. We could live with this, or manually change one of them.

So … I’ve rambled on long enough. Here is the video and a link to

Download the
Cafeteria Randomizer

That’s it! Thanks again to Mr Excel and Kevin Lehrbass.
Please comment, ask questions or share any ways you’d expand on this Cafeteria Randomizer.

photo credit: puritani35 via photopin cc