13 Jan 2014

## Video 00073 Helping Dean Pelton with his menu in Excel

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

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

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

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 🙂

1. Oz du Soleil says:

Yo! We’re thinking in the same direction. I like your weighted probability approach.

I just posted a video where I give props to you and Bill, and share a solution of my own. http://youtu.be/kDeY8H7nzmw

Basically, I look at further challenges in just randomizing.
– The specials are in a table so that we can add new specials easily.
– Formulas refer to the table instead of a static array.
– A little VBA code to get the RANDBETWEEN to hold still while we grab our list.

1. Kevin says:

Thanks for sharing Oz. I saw your video. Very cool.
It’s always good to share ideas!
Cheers,
Kevin