Recipe nutrition database/pivot table

[BrianP475]

Im not new to excel but very new to tables, databases, and pivot tables. My knowledge base is very limited on what Ive done so far. I am trying to develop a way that I can choose certain recipes which contain the ingredients, and their nutritional values. I'd like to pick the recipes, or maybe just the one food like yogurt, or a banana for breakfast. A recipe for lunch, and dinner. Then have excel give me the total of all the nutrients for that day. This would help me track my intake to help lower cholesterol, and my blood pressure. I read how to create a database which was just inputting the data then creating a table. That was easy enough, but I do not know how to use this table to perform the functions I want. 

[#2]

@BrianP475 

You'd help us help you by posting a copy of the workbook you have created, with the database on recipes and associated nutritional variables.Use the drop and drag at the bottom of the full text edit boxes here in this forum, 

or, if that doesn't work, post a copy on OneDrive or GoogleDrive and paste a link here that grants access.

Or if you enjoy the challenge of figuring it out with just a pointer or two--since you are familiar with Excel--let me point you to what I suspect are the functions that would yield the results you desire.

1. FIrst, a relatively recent function in Excel is the FILTER function, described here and demonstrated here.
2. That could be used to extract data regarding the specific things you'd consumed on any given day or period.
3. The results of that FILTER could be further analyzed by nesting the FILTER within SUM (and I'm sure you're familiar with that), to give a total of calories or other variables that FILTER has isolated.

[#3]

@mathetes 

I will try to get that when I get home. So far the table is relatively incomplete while I tried to figure it out. Thank you for trying to help. 

[#4]

Doesn't need to be complete.....just complete enough (say ten rows and a fairly representative set of columns of the various nutritional dimensions--calories, vitamins, carb counts, whatever--that you'd want to track per serving)

[#5]

https://1drv.ms/x/c/5c44253842f5d23a/EfAAk3CHyc1NstyviJj9K9sBf9Xvz2poWgu8Uiw8xrMalg?e=EsS0CC

[#6]

It took me awhile to figure out how to send the link, but hopefully it worked. I do use SUM quite a bit, but nesting the filter within SUM Im unsure of. Unless you are just saying to do this
=SUM(Filter)

[#7]

I can see that I will need to remove any letters from the numbers for each nutritional value. Now I just have to figure out how to add each recipe to the pivot table so that I can then filter it by meals.

[#8]

@BrianP475  

Do NOT add each recipe to the Pivot Table. In fact, for the time being forget about the Pivot Table. That may well not be the most suited way to summarize this data.

What we need to work on first is a database that includes ingredients from multiple recipes--a fairly large number of --and figure out what you want to achieve. On the attached, I've modified your database (which looks to me to be ingredients for a single recipe--is that right). Which means you'd always be using everything on that list, which is fine for some purposes, but maybe not for just coming up with all those counts for all the ingredients. If they're all used every time, just calculate those totals once per recipe, and just name the recipe.

All of which is to say, there might be two databases, one which is at the ingredients level of granularity, the other at the serving or dish level, in effect a summary of the first. That can easily be done. It's a matter of design.

Now, as far as SUM and FILTER, I can think of two ways one could approach this. But first, yes, you need to get rid of the letters connected to the ingredients in your database. Something that's always measured in grams, just enter the number; something always measured in quantity (e.g., 1 onion, or 2 eggs) just enter the number, and so forth. Perhaps the easiest way to sum them, once that's done, is as I've done on the dashboard sheet in this demo., which is do sum the numbers above the FILTERed table. I use the two Fat columns to illustrate. 

[#9]

Hi,

select * from nutritionData limit 20;

select * from recipesData;

cli_one_dim~nutritionData~2;

//select * from nutritionDataunion;

create temp table aa as 

select Ingredient,属性,数量,cast(数量 as float) qty,regexp2('\D$',数量) unit from nutritionDataunion;

select * from aa;

create temp table bb as 

select * from recipesData join aa on Ingredient like f02;

select * from bb;

create temp table cc as 

select f01,属性,sum(qty*f03)||unit total from bb group by f01,属性;

select * from cc;

cli_build_two_dim~cc~属性~total;

select * from cc_two_dim;

 

[#10]

@BrianP475 

Another thought regarding the design of your database (assuming that I'm right, that what you've done so far in that demo file was just a single recipe for a single dish),

Add a first column that names the dish. Copy the same name to each row of the ingredients. And do the same for each dish in your repertoire. You could then easily select the dishes to be served and get the totals of all the various nutritional dimensions for each of the ingredients for any combination of dishes.

[#11]

Ill try these when I get home. Lets say I have 20 recipes. I add the recipe name above each value. Then do I highlight all the cells at once to create pivot table? Or do I have to make multiple tables?

[#12]

@BrianP475 

Not above each value. A new column, not a new row. I'm recalling that you said at the start of this thread that you're not new to Excel, but are new to tables and pivot tables. The latter, the Pivot Table, was first introduced during the era of Lotus 1-2-3. It became a wildly popular tool, for good reason; it could summarize data  in a useful manner without the use of any formulas. To this day, one of my most useful personal Excel workbooks relies on the Pivot Table--it summarizes by month and budget category all of our personal income and spending, whether in checking accounts or credit cards. I'm not opposed to Pivot Tables.

But as I said, I'm not at all convinced that Pivot Table is the right tool for your purpose. There are other tools, tools introduced in the last few years, that provide much of the same kind of summarization, with even greater flexibility and customization. So please don't get fixated on that specific tool as what you need.

As it happens, I'm not totally clear on your purpose.

Maybe you could back up and explain the big picture here. Are you a chef? Do you manage a restaurant and desire to be able to accompany each menu item with salient nutritional details? If so, how detailed does that need to be; how precise?
What have you been envisioning as the purpose for the database you have? I can see using it to calculate (as I've hinted at) nutritional info for a dish, but at that higher level, rather than requiring the highly specific data on each ingredient IN the dish (although that might be a useful step, to be able to validate the outcome if asked).

You would do well to do some research on how Excel Tables work, and/or on database design. Here is one resource to start with. And YouTube has a lot of videos you might find useful.

The basic design is important, vital in fact. Don't try to get to the end product (which is where the Pivot Table or something else might come in to play) before building a solid foundation. A good part of my professional career involved designing a major database for the corporation where I worked. I retired over twenty years ago, but still have a good grasp of most of the basic concepts. If you can hold off on trying to move too fast toward the end here, and back up to describe in plain old conversational language what the information is you need to assemble (the input end, raw data end) and then what the output will be used for, then we can start designing. Excel, like other powerful tools, can do wonders; mis-used, it can create havoc.

I've attached a document on design; an old document, but one worth keeping around for reference, all the more so the more important the workbook you're trying to create.

[#13]

No not a chef. Im a maintenance manager that dabbles with excel to show information to my crew with graphs. Someone at one point created a pivot table that I use to see our downtime. I also like using excel to automate some math functions. This is a new realm for me in excel. What I would like to be able to do is pick what Im eating each day to make sure I dont go over daily values. So lets say Im going to the store. I pick what Im going to eat each day, check the values, and also know what I need to buy. We make most of our food from scratch. No boxed dinners and limit the amount of processed ingredients. Thats why the recipes would have the ingredients. I will be more than happy to use any tool that best suites this. That is why I reached out to ask. I very much appreciate your help. Ill look all these ideas over once I get home. You've provided a lot of information to go over.

[#14]

@BrianP475 

Thanks, that's a helpful description of the context. I take all that to mean that this could be an informal record keeping solely for your personal use. In that case, you might look for nutrition tracking spreadsheets that others have already created. Weight Watchers might have something (a friend profited from their disciplines toward losing weight, and i know some of it involved tracking things as you're doing).

In other words, you don't need to re-invent the wheel, although it is fun to tweak and improve what others have done,

[#15]

https://1drv.ms/x/c/5c44253842f5d23a/ERuw6QnAvj9FuyKVpA797lEB-pS6JI8SnN_IZt8lkLrsoA?e=AWzO7y

This will get me by once I input all the data. I knew it would be something simple like you explained for just putting the same name of the recipe in each row for its ingredients. Now that I can see how this can work I still want to look at the other things you suggested. Thank you for your help