Recipe Conversion.... is this even possible????

[brockk]

Hello Everyone,

I am working on a recipe cost template for my wife's soon to be Bakery. In this template I have columns E & F where she would enter in column E - Unit Weight of the Purchased Item (ej: Qts., Gal., Lbs., Kg., etc.) and in column F - Minimal Conversion Weight (ej: if in column E she entered either Qts, Gal, Cups then it would convert all into LIQUID OUNCES (Column G), as I am trying to calculate the actual cost per item in the yield of the recipe and if she enters for ej: Lbs, Kg, Tsp, Tbsp then it can convert it into OUNCES (Column G) without me having to enter the conversion type manually into Column F.

I guess that what I am trying to state is if there is any FORMULA that I can enter into Column G that can convert the data entered into Column E and that it can recognize to convert it all into either liquid ounces or weighted ounces? Below I left some dummy data so you can better understand what I am trying to accomplish. I am using Excel 2016 and Windows 10 if this helps. Hope to hear from someone soon, thanks.


A B C D E F G
Date Updated Ingredient Price Paid Quantity Unit Weight Conversion Weight Conv. Wgt. Cost
2/5/2017 All Purpose Flour $1.72 4 Lbs. Ozs. $0.03
2/5/2017 Self Rising Flour $3.24 2 Lbs. Ozs. $0.10
2/5/2017 Salt $0.89 26 Ozs. Ozs. $0.03
2/5/2017 Butter $0.99 1 Lbs. Ozs. $0.06
2/5/2017 Oil $7.99 1 Gal. Fluid Ozs. $0.50

 

[cchavez24]

Sure you can,

Here is how I would approch the problem.

On a seperate sheet I would make a table with all of the large units in one column then the word dry/wet based on what was used and in the third column the conversion rate.

You can then use a vlookup formula in the original sheet to show the wet/dry status (if you want, not needed for the math) and the conversion number in the cell you want the oz number.

i.e. ='cell refrence of large number' * vlookup(conversion rate)

Hope this helps, if you have questions, let me know.

 

[brockk]

Thanks for your quick response cchavez24.

I took into consideration your suggestions and after some additional work, I managed to make it work. I appreciate you taking your time to help me out. Now on to seeing what other types of improvements will be needed to make it more effective for her practical needs. Thanks again.

 

[pile-it Mark]

would you share your work? i have been asked by lunch ladys at a public school to modify their inventory sheets. they currently only list names and have no running totals. i am trying to make them interactive with scheduled lunches for ordering and the portion parts are kicking my butt