Dean Pelton has a problem.

His menu is alphabetical and has too many meatball recipes.

Watch as Bill Jelen (aka Mr Excel) helps Dean:  Mr Excel helps Dean Pelton

But how can I help Dean?

Let’s assume the students and staff at Greendale Community College want a variety of menu items but prefer some items over other items.

We could use discrete probability to make their favorite lunch items appear more frequently while still having variety in the menu.

Download my Excel file

Click here to download the Excel file I used in my video.

Watch my YouTube video

So, if Jeffery can’t carve out time to help Dean learn Excel then I will.  

Follow the steps below

Step 1 List the menu items in column F. In column E assign a number.  Higher numbers for items that people love. Lower numbers for items that people like (or 0 to remove the item).  Add a simple SUM function after the last one to get the total (cell E26).

Step 2 Add a counter in column B.

Step 3 A formula to divide each individual item by the total weight.  Like this:  =E3/$E$26  and drag it down the column until the last item.

Step 4 This is one of those magical Excel moments!  On the Excel ribbon: (i) select ‘Data’, (ii) ‘Data Analysis’ (if it’s not there read this), (iii) ‘Random Number Generation’ and click ‘OK’, (iv) click on the image below and set-up the parameters to match (my video explains each option).

Your set-up should look something like this pic (click to enlarge).

However, when you re-run the solution you will get a different set of menu items which is exactly the variety that Dean needs. Dean will have the perfect menu mix!

Click this pic to see the solution.  

You could modify this solution for other important needs like (i) speed dating, (ii) wine selection, and (iii) comic bookstore visits.

What are the ingredients to my solution?

• Counter
• Simple formula like this =E3/$E$26
• Data Analysis Toolpak (“Droolpak” for Excel nerds)
• A lookup (index, offset, or vlookup) 

How can you help Dean learn Excel???

Subscribe to my YouTube channel and learn more!

About Me

My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.  I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst.

There are so many amazing things that you can do with this powerful software. Check out my videos and my blog posts.

Away from Excel I enjoy learning Spanish, playing Chess, hanging out with Cali and Fenton and reading Excel books 🙂