With help from this forum, I've been working on an inventory sheet to schedule and track multiple ingredients used in several recipes.
This sheet contains 3 tabs that I'm currently working with...
The "Recipe" tab which contains the list of recipes and how much of each ingredient the recipe uses.
The "Schedule" tab which contains the date and the user input of what recipe will be produced that day.
The "Inventory" tab which the user inputs the starting inventory. This tab will then calculate the remaining inventory based on how much of each ingredient is used by the recipe that was produced on any given day.
On the "Schedule" tab, the remaining inventory of each ingredient is pulled from the "Inventory" tab and displayed. My goal is to add a "Days on Hand" feature that will display the maximum number of days that the scheduled recipe can be produced before one or more of its ingredients' inventory drops below 1. (In the attached example this column is highlighted in red)
My current plan was to add a column behind each ingredient column that would calculate the days remaining of each ingredient with the following formula: (These are highlighted light blue in the example)
=IF(VLOOKUP([@Recipe],Table1[#All],2,1)>0, [@402449]/VLOOKUP([@Recipe],Table1[#All],2,0),[@402449])
If the recipe calls for an ingredient, the days on hand for that ingredient will be calculated by taking the inventory/the amount the recipe calls for, otherwise just the existing inventory... (this is where I think my problem is)
Then run a MIN function in the "Days on Hand" column. The problem that I'm running into is that if the inventory of an ingredient is 0, the MIN function will still pick it up even if the scheduled recipe doesn't call for that ingredient. I am looking for a formula that will only consider the ingredients that have a value greater than 0 for each recipe in the "Recipe" tab.
Hopefully the attached example can be more descriptive. Thanks for any help!
Bookmarks