Have you ever over-engineered something?

 

Ever since I had a kid, I’ve relied on Googling every few months to make sure that I’m giving my kid an age-appropriate diet. Sometimes it’s hard to keep track of how much they’ve eaten of each food group. I recently learned how to solve optimization problems in Excel, so I broke down my toddler-diet-optimization problem into a set of variables and constraints, and asked Excel to solve it for me. Let me share with you what I did.

Download this Excel file and follow along – we’re about to do some Math!

We’re going to use an Add-In called Solver for our optimization. The Sheet titled Simple Example for Optimization explains the basics of how to use it. When we are trying to optimize something, we need to know the relationship between the thing we are trying to optimize (our target variable) and the factors that contribute to it.  

A simple example: let’s say that I own zero books, and that I want to own ten books at some point. The problem that we are trying to solve can be phrased as: How many books do I have to buy in how many years in order to eventually own ten books? I can either buy ten books immediately, or five books over two years, or two books over five years, and so on. There are clearly various ways to reach this target and they are all different iterations of the equation for a straight line: y=mx+c, where y is the target number of books, given a starting number of books (c), a rate of buying new books (m), and a period of time (x). If we want a particular value for y, we can change the values for m, x, and c to get to that. We can also add constraints for any or all of m, x, and c.

In the spreadsheet, I have set up the equation for the target variable y in row 13, the other variables in rows 14, 15, and 16. To use the Solver Add-In (assuming that you have installed it from the link that I’ve provided in the spreadsheet), you simply have to go to Tools > Solver, and reference the correct cells in the dialog box that appears. Set E13 (the cell that contains the equation) as the objective and set a target number; you can either set a particular value for the target, or you can ask Solver to minimize or maximize this value. Then, select E14:E16 as the variable cells whose values can be changed, and click Solve. Solver does the magic for you.

Try it! I’ve shown you a couple of examples of different constraints where I’ve said that I can buy more than 2 books a year (I14>=3), or that I can buy only between 1 and 2 books a year (L14<=2, L14>=1). You could try experimenting with the different constraints to see what happens.

 

So the gist is, once you have an equation and all its dependencies, you can optimize anything! Now, on to our diet optimization, which is a slightly more complex problem. What we want to know is: how much of each food to eat, to get the required amount of calories, protein, calcium, vitamins A, C, D, E, and minerals like iron, potassium(K), magnesium (Mg) and Zinc (Zn). You could potentially expand the list of nutrients, add in fats and fiber and what not, but I’ve done a “smaller” scale optimization here, based on what’s critical to a child’s development.

 

The sheet called Nutrition Info contains some common foods from different food groups, along with USDA reference values for the nutrients that we are optimizing for. The sheet called German Nutrition Society contains the recommended daily requirements for each of these nutrients. These are more or less the same for most countries, feel free to change these numbers if they are different where you live. I have included both the per day values and per month values. I’ve optimized for only 25 days in a month because we do eat out a fair bit! Feel free to change this number in cell C4, it should change all the values in cells C5 to C15.

 

Finally, the Variables and Constraints sheet contains the data to be used in the optimization. I have used the total calories consumed in a month (cell C2) as the target variable. You can use any of the other variables as a target variable too – e.g. total calcium, or total protein, and include calories as one of the constraints. (You can also add in a column for Cost in the Nutrition Info Sheet and ask Solver to minimize Cost, while achieving adequate nutrition.)

 

I also have each of the foods from the Nutrition Info page and a column of numbers adjacent to them. This column (C14 to C58) is the number of times each food is consumed in a month. This is what we are going to use as our variable cell range. How many times in a month should each food be eaten in order to achieve the target number of calories, while also meeting the requirements for protein, calcium, iron, etc.?

 

The equation that we need to use is called a SUMPRODUCT in Excel. The formula requires two arrays of the same size: each cell of the first array is multiplied by the corresponding cell of the second array, then all the products are added up. What the equation does here is, it takes each food’s number of calories, multiplies it by the number of times it’s eaten in a month (its frequency), then adds up all the calories from all the foods. In other words, it’s a sum of all the products.

 

 Cell C2 is the sumproduct of all the different foods’ frequencies and the number of calories they provide, sourced from the Nutrition Info sheet. Similarly, cell C4 is the sumproduct of all the foods’ frequencies and their protein content. And so on and so forth for all the nutrients in cells C5 to C13. If you want to see which cells contribute to each equation, you can use Formulas > Trace Precedents to map this. Remove Arrows will undo this action.

 

When you start off, you can input “0” as the value for all your “variable” cells, i.e. C14 to C58 in The Variables and Constraints sheet. After you use Solver, these numbers will change. Make sure that cell C2 is an equation, and not a number. I have included the range of values or ideal values for each nutrient that I’m optimizing for, in columns D, E, and F. Column G indicates whether the optimized diet has an excess or a deficit of a particular nutrient.

 

When I used Solver the first time around, I did not add any constraints. I simply asked Solver to optimize for calories, given the foods that I had in the diet. Because Solver derived a large portion of the calories from 13 cups of sweet potatoes, there was a great excess of vitamin A in the diet (cell G10). So, in round 2 (columns I to N), I included constraints for maximum recommended intake values for some of the nutrients. You should be able to see this: go to cell J2, then go to Tools > Solver. You can see what constraints are included in that optimization. This gave me a successful iteration where there was no calorie deficit, and the nutrients mostly fell into the desired range. This also left me with no doubt that you need a supplement for vitamin D, especially if you are the kind that makes melanin!

 

You can further refine this diet by adding constraints for the number of times a particular food is eaten, or by leaving out entire food groups (due to a dairy allergy or a religious diet, for example). All you need to do is play around with the constraint variables until you get a diet that sounds about right for your kid!  You can also optimize this for adults, or pregnant women, or for keto/paleo diets. You need to change the Ideal/Min/Max Value columns in the Constraints and Variables sheet, and add whatever foods you regularly eat to the Nutrition Info sheet, and you’re now good to go!

 

I hope you found this useful, and I hope you spend hours playing around with Solver, like I did :)