Forum Discussion
Raul999
Dec 12, 2023Copper Contributor
Need help with a food costing chart I am working on. Stuck at two functionalities.
Have attached the sheets I am working on along with the flow I am trying to achieve.. Could some one help me out. The aim is to fetch ingredients from the ingredient's. sheet and automatically add the price/cost according to the [unit used/cost of procurement]. There are two sub sheets. One is sub recipe and main is recipe. On the main recipe its should be able to fetch both ingredient's as well as sub recipe along with the same cost logic as the first case. Is it possible?
The case flow I have in mind:
Sheet by sheet images:
FILE LINK:
- Harper_King_1Copper Contributor
It sounds like you're working on a complex chart with multiple functionalities! To help streamline the process, you might want to consider using a more advanced formula or possibly a script to automatically fetch ingredient prices from the ingredient sheet. Also, for a more customized experience, have you considered exploring different 7 Brew Secret Flavors that could inspire more efficient ways to manage your cost structure? You can find more details and examples to customize your flow in this helpful resource.
- mathetesSilver ContributorCould you link to those files on GoogleDrive instead of the one you're using. It comes up with a big ad and agreement to terms of service just to look at your file. I don't care to go through that....
- Raul999Copper Contributor
mathetes Hey thank you for your response. I have attached the same on google drive here.
- mathetesSilver Contributor
You need to clean up your raw data a bit before taking it further. I've given you a formula that works to calculate price of one ingredient, but in the process of doing that I ran across several issues that you need to clear up.
Quantity: the more consistent you can be the better. Eggs are listed (in the ingredients sheet) as 48, i.e., a count of number of eggs. But all the other quantities are in grams. And in order to do math, you need to have the grams in there as numbers, not as text with the initials "gm" attached.
Then on your recipe sheets you show quantity and weight as separate columns, but if quantity is already measured in grams, then you don't need "weight" as well. Or volume. Unless those are meant to be calculated based on number of portions. Either way, it needs to be clear.
I made the "Ingredients" sheet into an Excel table. That way it can be easily referred to in INDEX and MATCH formulas, as I've done in column E of the "Sub Recipes" tab.
Beyond that, however, it's not clear to me what each different portion of the Sub and Recipe tabs are meant to do. If you need additional help, maybe you could fill out the raw data to represent a real recipe and number of portions, etc., i.e., fill out the raw data and then identify the cells that need to be calculated and how they relate to one another. I don't think this is a difficult task, but you need to explain it more clearly, after you've cleaned up the distinctions between quantity weight and volume.