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
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?
- 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.
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.
- Lasagna twice AND just 2 days apart.
- 4 meals are repeated within 5 days.
- Pho 2 days in a row.
- Broiled Chicken 2 days in a row.
- Fried cod twice within 5 days.
- 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
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.