Warning: mysql_real_escape_string(): A link to the server could not be established in /home3/dragon99/public_html/wp-content/plugins/easy-contact-forms/easy-contact-forms-database.php on line 152
Use Excel to Randomize Lunch: Haggis, Anyone? - Oz du Soleil
Select Page
Today, we look at a basic need and take it a few steps further. The result is a mini app for randomizing meals.
We won’t get into the dirty how-to of formulas. Instead, we’ll think through what’s needed to make something really work for a repeated task.

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

## 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