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…
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
- Add your nutritional requirements to the “DietaryRequirementsTable”
- 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)
- 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).
- Specify what constraints you would like to apply when it calculates a recipe in the green cells in columns Q & R.
- 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.
- 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!