Self Optimizing Excel Spreadsheet (optimize automatically for nutritional composition and price)


#1

I’ve been playing around with different versions of soylent for a while and like many of you have ended up with a spreadsheet. I wasn’t going to share this as it seemed like there are enough spreadsheets out there already, but noticed that most of the spreadsheets are lists of ingredients with some basic maths. Also this thread made me realise that someone else might be interested in letting a computer do all the hard work in creating a formula…

Here is the file

This is by no means perfect, but for the Excel geeks out there it may be of value. Let me know what you think and feel free to use it and modify a copy.

(Very) Basic Instructions

  1. Add your nutritional requirements to the “DietaryRequirementsTable”
  2. Add your ingredients with their nutritional profile either from the SR25 database (the whole database is included - apologies for the large file!), or add your own. (See the technical note below for more details)
  3. On the “Formulator” sheet, specify a range for the ingredients you want in the min and max sections of the “Recipe” table. You can also specify an amount in the “amount/day” field (or just leave it blank and let excel work it out for you).
  4. Specify what constraints you would like to apply when it calculates a recipe in the green cells in columns Q & R.
  5. Use the built in Excel Solver (google it if you are not sure - it needs to be enabled if you haven’t already). This can be used to find the most nutritionally complete combination of ingredients (by maximising R15) or by minimising the cost (by minimising Q21). I normally play around with it by adding/removing ingredients, then maximising for the nutritional contents, then minimise the costs. NB, you need to add a contraint when minimising the costs to not reduce the “Requirements Satisfied” in cell R15 if you want to use it like i do.
  6. Sense check the results (i sometimes find i need to run the solver a few times before it stops finding better solutions).

Technical notes:

  • Ingredients need to be added in several places before the spreadsheet will work. There is an array formula in ContentAnalyser[Total From Ingredients] which is very easy to break (if you can think of a better formula please let me know!). To keep it working you must ensure that every ingredient is included in order in each of the yellow worksheets (except DietaryRequirementsTable). If it is missing or out of order it will probably break! The nutritional details for each ingredient go in the “NutritionalCompositions” sheet and you only need to add the ingredient name to the similarly named “NutritionalComposition (T)”.
  • The spreadsheet was created in Excel 2013 running on Windows 7. I believe it should still work with Excel 2007 onwards but have not tested it.
  • There are various look-ups and lists in the workbook but are not necesssary to its functioning.
  • The whole workbook is heavily dependant on Excel’s built in tables feature - all the calculations use the automatically created ranges that result from this. If you copy/pasted data in, ensure it has been encorporated into an Excel table and is not just sitting as data in some cells.
  • The solver is configured to use a genetic algorithm to try and find the best possible solution for the constraints you give it. The number of potential solutions is shown in cell Q23 on the Formulator sheet. It tends to be a very big number!
  • I am unlikely to have the time to help people use this spreadsheet so only attempt it if you are happy making things work in Excel. If you have a problem feel free to post it below and I’ll do my best to respond to you.

Enjoy!


Algorithm to solve for ingredients
Creating program to automatically sort out and reduce the cost of creating your own soylent
#2

I didn’t know this was possible with Excel ^.^


#3

Holy crap, a 43 MB soylent file. Wow. That’s insane man, great work.

Edit: I’m unable to open it with excel 2007. I’ll try a few more times before giving up, and report back.

Edit2: Finally opened after sitting there for ~5 minutes or so. Yay!


#4

Glad you got it to open. Hope it works for you. The SR25 database is what makes it so big. If you delete/move the sheets that start “SR25…” and save that it will be a lot smaller and will also load a lot quicker. There are some hidden pages too which you need to delete.

For info, it takes ~55 seconds to load on my 4 year old i5/4GB laptop. And around 30s every time the excel solver initialises a problem.


#5

Just downloaded it, to see the loading time - 43 seconds, the PC is 3 years old, but pretty good (X6 1090, 16gb ram etc)

You included 8194 different foods, some of them turtle, whale eyes, whale liver, seal, owl. You are f***ing mad! :heart:

Edit: I’m insulted because you didn’t include my favourite cheese, Emmentaler,


#6

Ha - I hadn’t spotted the whale eyes or owl before. I wonder how they ended up including those in the database. Not sure it would make the best smoothies, especially the beak…


#7

Being a data fiend this excel document is a dream come true :slight_smile:

One issue is this spreadsheet doesn’t currently track Energy? How would I go about adding this without breaking it? Would just added it as a nutrient in the dietaryRequirementsTable be enough?


#8

There is a summary for energy on the formulator page (you could actually use the solver to set limits around the calorie composition too - hadn’t really thought about that).

There are a few ways depending on how you wanted to track it. I assume by energy you mean something like calories (but let me know if not).

My thoughts are: replace one of the summary requirements with “Energy” or add a new one. The field would need to be updated in the two nutritional composition tables and the content analyser table (the SR25 convertor too if you use it). Most of the calculations work from the NutID field so you could just rename one of them quite easily, otherwise you will have to create a new ID in each table. You also need to modify how the field calculates in Nutritional Composition (T) depending on what you want to do with it.

HTH


#9

This is a little remedial, but what exactly should we add for dietary requirements in DietaryRequirementsTable? I notice there are some pre-defined values for “Version 1” and “Version 2.” Should the maximum values for these nutrients reflect our dietary needs, or should we supplant the values assigned to Version 1 or 2?


#10

Probably just use this to get you started with the numbers:
http://fnic.nal.usda.gov/fnic/interactiveDRI/

The “Versions” that were already in there are just me playing around. The numbers need to be customised to you. The upper limits too. You can create multiple versions if you want to (e.g. gym day, non-gym day). For simplicity you only need one though. You can then specify the version the formulator uses in the formulator configuration section.


#11

Oh wow @DRhino, this is amazing!
But for excel noobs like me, wouldn’t there be an easier solution which doesn’t include the SR25?

Wouldn’t it be possible to only use 5 tables: Requirements, Recipe, Nutrient Analysis, Nutritional Composition and Configuration?

I’d really like to manually add the ingredients with their nutritional values and be able to solve everything myself, but I don’t understand how to create/modify the Excel tables, neither do I understand how to link the tables with one another.

Could you help me out on that?
Or could you please recommend any good website on which I could learn how to use Excel like you do?

Thanks!


#12

The owl, turtle and whale are probably in there because (if memory serves) the USDA database includes a section of Alaskan native foods, for some PC reason or other – just Alaskan natives, mind you! Anyway, there’s your mystery solved. :slight_smile:


#13

@AndreM

But for excel noobs like me, wouldn’t there be an easier solution which doesn’t include the SR25?

the database just makes the file bigger, not harder! You can delete it out quite happily if you don’t want to use it. I tend to use it when trying to understand the effect of adding food to my concoction as it is a much better source of information than food manufacturers - if you are just using chemical compounds, its much easier to just type in the numbers directly.

Wouldn’t it be possible to only use 5 tables: Requirements, Recipe, Nutrient Analysis, Nutritional Composition and Configuration?

… more or less. “Configuration” is not an Excel Table in the same sense as the rest. You also need:

  1. “Ingredients” (for cost analysis, getting the quantities used of each
    ingredient to calculate correctly and working out re-ordering
    points)
  2. “NutritionalComposition(T)” (for most of the summary calculations like omega ratios and total calories, and the array formula in
    ContentAnalyser[Total From Ingredients])

I’d really like to manually add the ingredients with their nutritional values and be able to solve everything myself, but I don’t understand how to create/modify the Excel tables, neither do I understand how to link the tables with one another.

You don’t need to do any linking. This is handled by the index/match (and similar) formulae that are already there.

To modify the values of one of the ingredients that is already in the tables you mentioned (plus my additions):

  1. change the name (in all the relevant tables) to whatever you want to add
  2. delete the old values from each of the tables
  3. add your own values in.

To add an extra ingredient:

  1. start typing in the next row/column after the end of the table you want to add it to. Excel should automatically expand the table to include it. You will notice the change in format as it does so.
  2. (Notice that ContentAnalyser[Total From Ingredients] will show an error until the ingredient is added to every table due to the nature of the array calculation.)
  3. Fill out all the values for your ingredient.

Could you help me out on that?
Or could you please recommend any good website on which I could learn how to use Excel like you do?

…google is your friend. It taught me all I know!

Good luck.


#14

Wasn’t there a poem that started like that…

… I think it involved honey and pussy cats too.


#15

Edward Lear, “The Owl and the Pussycat,” lol. They had their own simple, primitive version of Soylent on their voyage, too!

“The Owl and the Pussycat went to sea, /
In a beautiful pea-green boat. /
They took some honey and plenty of money /
Wrapped up in a five-pound note.”


#16

DRhino: there is no permission on this file to download it. at the same time, google drive does not know how to open it. can you please check the security settings?


#17

Me neither. I got all excited to use it, then I couldn’t :frowning:


#18

@movgp0 @tytanium

Not sure what went wrong. Just checked the settings, they seemed fine, turned them off and on again and it seems to be working. Let me know if not.


#19

Thanks! it works now :smile:


#20

I can’t get to the file, for some reason. Can you make it available again? I’ve tried everything I know how to do and it won’t let me download the file. Thanks!