Forum Discussion
Recipe nutrition database/pivot table
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.
- FIrst, a relatively recent function in Excel is the FILTER function, described here and demonstrated here.
- That could be used to extract data regarding the specific things you'd consumed on any given day or period.
- 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.
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.
- 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)
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;
- 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)- 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.
