Forum Discussion
Recipe nutrition database/pivot table
=SUM(Filter)
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.
- 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?
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.
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.
