Selecting lines based on number of times selected in past.... or something along those lines!

[Peter.Stevens2]

Hi Excel Wizards and Guru's, in my never ending quest to make my life easier I've developed a spreadsheet which contains recipes of the meals I like eating. I've then written a series of macros/forms to create a meal plan for the coming week which then generates the shopping list of the ingredients I need to buy.

My problem is that the planner selects recipes based on random number generation so I'm often getting the same meals week after week. Ideally I'd like to put some sort of validation in so that meals that have been selected less often would have a higher probability of being selected when I generate a new plan, thus putting more variety in the meals that are selected and making sure that I get my 5 portions of Fruit and Veg a day!

If anyone wants a copy of the file once it's complete, PM me and I'd be happy to forward one along.

I guess it's a crude for of MRP for my kitchen...Any help you could offer would be greatly appreciated! Thanks!

 

[JackDanIce]

How about assigning a count table that records the number of times a meal is selected and then applying a weighting function to that to reduce probability of that meal being selected? Something along the lines of:

If Meal count >3 Then
(Meal) x (random variable) x (0.1)
Meal count (for that specific meal) = Meal count (for that specific meal) - 1
Else
(Meal) x (random variable) x (0.9)
Meal count (for that specific meal) = Meal count (for that specific meal) + 1
End if

You could then update by replacing 3 with some kind of incremental counter so as a meal becomes less selected, the chances of it being selected in the future increase and those selected more often decrease.

Someone asked a similar question a while ago about randomally generating dice rolls, but with a reduced weighting for numbers 5 and 6. I posted an answer to that if you want to search and modify.

 

[Peter.Stevens2]

Great, Thanks that's a brilliant solution!

link to the previous thread is here if anyones interested :http://www.mrexcel.com/forum/showthread.php?t=458847&highlight=probability

Thanks for the quick reply, I was getting hungry and the thought of eating yet another bacon salad was driving me to insanity! Maybe I rely too much on excel!!

 

[JackDanIce]

That's the one, just had a look at that thread - happy memories!

What about adjusting your code to consider different world cuisine's too - e.g. if you have a desire for an Italian or Mexican or Indian meal? I'm sure you could easily build that in?