long shot formula help

[cheryplee]

this may be a ling shot but i have racked my brain on this and am getting a headache.

what i need to do is find a combination of 3 food items that equal all three catagories within 20%

these numbers are below

for example if i needed something with 8 hunger, 7 health, and 2 happiness then it would return
almonds, apple, apple juice
and any other combos of 3 items that when combined have 7-9 hunger, 6-8 health, and 2 happiness

Hunger Point Boosts:
Almonds - 2
Apple - 4
Apple Juice - 2
Asparagus - 5
Bagel - 5
Baked Beans - 7
Baked Potato - 6
Bananas - 3
Blueberries - 2
Blueberry Cheesecake - 4
Bottled Water - 1
Bread - 6
Broccoli - 2
Burger - 10
Cake - 3
Candy Apple - 2
Carrot Cake - 10
Carrots - 4
Cheeses - 4
Cherries - 4
Chicken Noodle Soup - 3
Chicken Nuggets - 9
Chocolate Bar - 2
Chocolate Milk - 2
Chocolate Milkshake - 2
Chocolate Pudding - 2
Coconut - 4
Cola - 1
Cookies - 2
Corn Flake Cereal - 5
Corn on the Cob - 3
Cream Soda - 1
Cupcake - 1
Dragon Fruit - 3
Eggs - 4
Fish Sticks - 10
French Fries - 4
Fruit Punch - 3
Fudge - 2
Ginger Ale - 2
Granola Cereal - 6
Grapefruit - 2
Green Grapes - 3
Honey - 2
Hot Chocolate - 2
Hot Dog - 7
Ice Cream Cone - 3
Ice Pops - 2
Iced Tea - 2
Jelly - 1
Kiwi Fruit - 2
Lemon Meringue Pie - 2
Licorice - 1
Lollipop - 1
Macaroni & Cheese - 9
Marshmallow - 0
Milk - 3
Mushrooms - 2
Nacho Chips - 6
Oatmeal - 3
Orange - 2
Orange Juice - 2
Orange Pop - 1
Pancakes - 6
Papaya - 3
Peach - 3
Peanuts - 4
Pear - 3
Peas - 2
Peppermints - 1
Pickles - 5
Pineapple - 4
Pink Lemonade - 1
Pizza - 5
Popcorn - 4
Potato Chips - 2
Pretzel - 3
Pumpkin - 3
Raisins - 3
Ramen Noodles - 7
Raspberries - 2
Root Beer - 1
Salad - 4
Shrimp - 7
Spaghetti - 12
Strawberries - 1
Strawberry Yogurt - 4
Sunflower Seeds - 1
Sushi - 6
Tacos - 8
Toffee - 1
Tomato - 3
Tomato Soup - 3
Waffles - 6
Watermelon - 4
Webkinz *******s - 5
White Rice - 7


Health Point Boosts:
Almonds - 3
Apple - 2
Apple Juice - 2
Asparagus - 2
Bagel - 1
Baked Beans - 2
Baked Potato - 2
Bananas - 3
Blueberries - 2
Blueberry Cheesecake - 1
Bottled Water - 3
Bread - 3
Broccoli - 4
Burger - 1
Cake - 0
Candy Apple - 1
Carrot Cake - 5
Carrots - 2
Cheeses - 1
Cherries - 0
Chicken Noodle Soup - 2
Chicken Nuggets - 3
Chocolate Bar - -1
Chocolate Milk - 2
Chocolate Milkshake - 0
Chocolate Pudding - 0
Coconut - 2
Cola - 0
Cookies - 0
Corn Flake Cereal - 2
Corn on the Cob - 1
Cream Soda - -1
Cupcake - 0
Dragon Fruit - 2
Eggs - 3
Fish Sticks - 3
French Fries - 0
Fruit Punch - 0
Fudge - -1
Ginger Ale - 1
Granola Cereal - 4
Grapefruit - 3
Green Grapes - 2
Honey - 0
Hot Chocolate - 0
Hot Dog - 0
Ice Cream Cone - 0
Ice Pops - 0
Iced Tea - 1
Jelly - 0
Kiwi Fruit - 2
Lemon Meringue Pie - 0
Licorice - 0
Lollipop - 0
Macaroni & Cheese - 1
Marshmallow - -1
Milk - 3
Mushrooms - 2
Nacho Chips - 2
Oatmeal - 2
Orange - 3
Orange Juice - 3
Orange Pop - 0
Pancakes - 2
Papaya - 3
Peach - 2
Peanuts - 1
Pear - 1
Peas - 2
Peppermints - 0
Pickles - 2
Pineapple - 2
Pink Lemonade - 1
Pizza - 2
Popcorn - 1
Potato Chips - 0
Pretzel - 0
Pumpkin - 2
Raisins - 3
Ramen Noodles - 2
Raspberries - 2
Root Beer - 0
Salad - 4
Shrimp - 3
Spaghetti - 3
Strawberries - 2
Strawberry Yogurt - 2
Sunflower Seeds - 1
Sushi - 3
Tacos - 1
Toffee - -1
Tomato - 3
Tomato Soup - 2
Waffles - 1
Watermelon - 3
Webkinz *******s - 0
White Rice - 1


Happiness Point Boosts:
Almonds - 1
Apple - 0
Apple Juice - 1
Asparagus - 0
Bagel - 2
Baked Beans - 1
Baked Potato - 0
Bananas - 1
Blueberries - 2
Blueberry Cheesecake - 2
Bottled Water - 1
Bread - 0
Broccoli - -1
Burger - 3
Cake - 2
Candy Apple - 2
Carrot Cake - 7
Carrots - 0
Cheeses - 0
Cherries - 1
Chicken Noodle Soup - 2
Chicken Nuggets - 3
Chocolate Bar - 3
Chocolate Milk - 2
Chocolate Milkshake - 2
Chocolate Pudding - 5
Coconut - 1
Cola - 3
Cookies - 2
Corn Flake Cereal - 1
Corn on the Cob - 1
Cream Soda - 2
Cupcake - 2
Dragon Fruit - 2
Eggs - 1
Fish Sticks - 1
French Fries - 1
Fruit Punch - 1
Fudge - 3
Ginger Ale - 1
Granola Cereal - 0
Grapefruit - 1
Green Grapes - 2
Honey - 2
Hot Chocolate - 2
Hot Dog - 5
Ice Cream Cone - 2
Ice Pops - 3
Iced Tea - 1
Jelly - 2
Kiwi Fruit - 1
Lemon Meringue Pie - 3
Licorice - 2
Lollipop - 2
Macaroni & Cheese - 3
Marshmallow - 4
Milk - 0
Mushrooms - 0
Nacho Chips - 2
Oatmeal - 0
Orange - 0
Orange Juice - 0
Orange Pop - 2
Pancakes - 2
Papaya - 0
Peach - 0
Peanuts - 1
Pear - 0
Peas - 1
Peppermints - 2
Pickles - 1
Pineapple - 1
Pink Lemonade - 1
Pizza - 2
Popcorn - 2
Potato Chips - 2
Pretzel - 1
Pumpkin - 0
Raisins - 1
Ramen Noodles - 2
Raspberries - 3
Root Beer - 1
Salad - 0
Shrimp - 2
Spaghetti - 0
Strawberries - 2
Strawberry Yogurt - 1
Sunflower Seeds - 1
Sushi - 2
Tacos - 4
Toffee - 3
Tomato - 0
Tomato Soup - 0
Waffles - 4
Watermelon - 1
Webkinz *******s - 3
White Rice - 0

 

[AnalyticsGuy7]

Ok, I have a VBA solution, but it takes a little while to run, and you need to setup your workbook the following way:

1. Create a sheet called "List" where you have all of your food items listed, starting in cell B2 and going all of the way down.

2. Column C has your Hunger values, D has your Health Values, and E Happiness Values. These should be parallel to your items in column B. At the top, you can have headers (Food, Hunger, Health, Happiness).

3. Also create a sheet called "Answers". This is where your combinations will pop out, along with your Hunger, Health, and Happiness scores.

4. Once the macro is done running, it will display a message box with the stats that you need to verify it was done properly.

5. I have added flexibility to the code so that you can add more items to the list if you'd like.


If you are new to VBA. Just hit alt + F11, and insert a new module. Place the following code in it, and hit 'run' (play button) AFTER you have your workbook setup properly.

You can adjust your targets and your deviance (.20) if you'd like:

Option Explicit

Sub ThePerfectMatch()

Dim Loop1 As Variant, Loop2 As Variant, Loop3 As Variant
Dim Rower As Variant
Dim Hunger, Health, Happiness, Deviation

Hunger = 8
Health = 7
Happiness = 2
Deviation = 0.2

Rower = 1

Sheets("List").Select

For Loop1 = 3 To Range("A" & Rows.Count).End(xlUp).Row - 1
    For Loop2 = 2 To Range("A" & Rows.Count).End(xlUp).Row - 1
        For Loop3 = 1 To Range("A" & Rows.Count).End(xlUp).Row - 1
            If Loop2 > Loop3 And Loop1 > Loop2 Then
                If Abs(WorksheetFunction.Sum(Range("C" & Loop1 + 1), Range("C" & Loop2 + 1), Range("C" & Loop3 + 1)) - Hunger) <= Hunger * Deviation Then
                    If Abs(WorksheetFunction.Sum(Range("D" & Loop1 + 1), Range("D" & Loop2 + 1), Range("D" & Loop3 + 1)) - Health) <= Health * Deviation Then
                        If Abs(WorksheetFunction.Sum(Range("E" & Loop1 + 1), Range("E" & Loop2 + 1), Range("E" & Loop3 + 1)) - Happiness) <= Happiness * Deviation Then
                            Sheets("Answers").Range("A" & Rower) = Range("B" & Loop1 + 1) & ", " & Range("B" & Loop2 + 1) & ", " & Range("B" & Loop3 + 1)
                            Sheets("Answers").Range("B" & Rower) = WorksheetFunction.Sum(Range("C" & Loop1 + 1), Range("C" & Loop2 + 1), Range("C" & Loop3 + 1))
                            Sheets("Answers").Range("C" & Rower) = WorksheetFunction.Sum(Range("D" & Loop1 + 1), Range("D" & Loop2 + 1), Range("D" & Loop3 + 1))
                            Sheets("Answers").Range("D" & Rower) = WorksheetFunction.Sum(Range("E" & Loop1 + 1), Range("E" & Loop2 + 1), Range("E" & Loop3 + 1))
                            Rower = Rower + 1
                        End If
                    End If
                End If
            End If
        Next Loop3
    Next Loop2
Next Loop1

Sheets("Answers").Select

MsgBox "Hunger Range = " & WorksheetFunction.Min(Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))) & " - " & WorksheetFunction.Max(Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))) _
& vbNewLine & "Health Range = " & WorksheetFunction.Min(Range(Range("C1"), Range("C" & Rows.Count).End(xlUp))) & " - " & WorksheetFunction.Max(Range(Range("C1"), Range("C" & Rows.Count).End(xlUp))) _
& vbNewLine & "Happiness Range = " & WorksheetFunction.Min(Range(Range("D1"), Range("D" & Rows.Count).End(xlUp))) & " - " & WorksheetFunction.Max(Range(Range("D1"), Range("D" & Rows.Count).End(xlUp)))
End Sub

Hope this works for you! (It was a nice challenge)

 

[AnalyticsGuy7]

I forgot to add that you should have the numbers in column A (1-97) in parallel with the items.

Otherwise, you can switch the code to:

For Loop1 = 3 To Range("B" & Rows.Count).End(xlUp).Row - 1
    For Loop2 = 2 To Range("B" & Rows.Count).End(xlUp).Row - 1
        For Loop3 = 1 To Range("B" & Rows.Count).End(xlUp).Row - 1
            If Loop2 > Loop3 And Loop1 > Loop2 Then

etc. etc.

 

[cheryplee]

wow that is great. Didnt think it would be an easy one.

just one questions.
i wanted to be able to change the needed health, happiness, and hunger values directly on the spreadsheet (instead of through the macro)

i tried assigning the spreadsheet values in the code but it gives me an error. This is the first time working with macros so i'm a little lost.


this is what i changed the values in the code to:
Hunger = List!j2
Health = List!j3
Happiness = List!j4
Deviation = 0.2

 

[AnalyticsGuy7]

You were close

Hunger = Sheets("List").Range("J2")
Health = Sheets("List").Range("J3")
Happiness = Sheets("List").Range("J4")
Deviation = 0.2