A good challenge, Turn Diet data into useable Data

Hi, I'm an intermediate Excel user but never used it for this kind of thing. My wife suffers from chronic stomach issues so I created a Google Form (questionnaire) that she fills out twice a day and we are tracking her symptoms on a scale of 1 to 10 and how they differ when she eats different foods, one week without one type of food, another without another type, etc.

I attached the results from her first week, this is when she went a whole week with no dairy. But aside from not eating dairy she could also tick off boxes of other foods she ate that are potential "problem" foods. How can I use that data since words and not numbers and they are in the same cell? Any cool formulas that can help me with that? Any way to quickly see that every time she ate nuts for example it effected her nausea or bloating...or any way to see that every time her average sysmptoms were bad, she always ate...whatever

Also, any general suggestions of good formulas I could apply to get the most out of all this data would be very much appreciated. I also know basics of graphs and charts but if anyone has any special suggestions of how to use graphs and charts to best in this kind of scenario I would love to hear it. Many thanks for your responses!

2u7uk2w.jpg

Thanks Ford, yea that is what I wanted to do but I couldnt think of a good (or smart) way to upload it. I have now uploaded a copy of it to GoogleDrive and made it editable and pasted the link below. But if there is a better way let me know.

As for expected results, Im not really sure how to express that in the actual excel file, but as I said in my initial post, I am most keen on trying to figure out how to make use of the data in the "What have you consumed since your last survey" column. But also how to use some graphs and charts to compare the week with Dairy vs. the week without "gassy foods". If someone who has a good head for this kind of thing (not just excel but analyzing data) takes a long look at the spreadsheet I am sure they could come up with a bunch of ways to squeeze useful data from it. Unfortunately Im not that guy!

Anyways, the link is below, as I sais any easier way to make it accessible for people let me know. And if anyone has any questions feel free to ask. Again, many thanks for any assistance!

-Jordan & Nadja

https://drive.google.com/file/d/0B0x...ew?usp=sharing

Ahh ok, I think I see it now. I attached it here.

Thanks for taking a look. Im already familiar with averages and planned to average them out (Although the formulas you used were different and more complex than I would have used...I only know the simple old =average formula).

As for the food types in one cell, yea, that is kindof the kicker. The thing is this is exported data from a GoogleForm survery I created. It has to be easy enough for her to complete the form in just a couple minutes while on her smartphone, GoogleForms makes this very easy, but for the type of food, I created a list of 30 foods and she ticks boxes next to the ones she ate, unfortunatly this is how it exports the data into one cell. I'm not sure I could change the form in a way that it would still be quick and easy to go through 30 options.

Ahhh ok, that is useful. Thanks!

Is there a way with excel to do an IF formula and then just have it it search for text withing quotes. So that IF the text "onion" appears in a quote then....etc. etc. ? That would be great but I suspect not possible.

I also thought of another way I might be able to tweek the form so that the data it spits out is easier to work with and it wouldnt be too much more difficult for her to fill out.