Recipe Scaling Question

[Gorango]

Hello everyone.

I have what I think is a pretty complex problem. I will try my best to explain what I would like to do.

I already have a template that can scale recipes, however my issue is when scaling to a large number lets say 1024 portions, the results are not intuitive. In that example the 512 teaspoon scales to 170 tablespoons + 2 teaspoon, which is a smaller measurement but it is not the smallest measurement available. For that example I would like it to convert to cups. But yet another example would be to convert 64 quarts into gallons using the same formula in another cell.

I am using Match and Index functions but I cant seem to wrap my head around doing conversions that can accommodate large scaling.

If its easier to understand what I'm trying to do I am more than will to share my spreadsheet, I just dont know how.

Let me know if there is anything else you need from me.

 

[oldbrewer]

[TABLE="width: 810"]
<colgroup><col><col span="11"></colgroup><tbody>[TR]
[TD]potatoes[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]204.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]peas[/TD]
[TD="align: right"]1.4[/TD]
[TD="align: right"]95.57333[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sausages[/TD]
[TD="align: right"]2.4[/TD]
[TD="align: right"]163.84[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]gravy salts[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]34.13333[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PORTIONS[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]1024[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]I wish to make[/TD]
[TD="align: right"]1024[/TD]
[TD]portions[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]scaling factor[/TD]
[TD="align: right"]1024[/TD]
[TD]/[/TD]
[TD="align: right"]15[/TD]
[TD]=[/TD]
[TD="align: right"]68.26667[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]can you describe your issue in terms of this example please[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Gorango]

I'm afraid I cant. That example does not make any sense to me.

I am dealing with whole numbers and a few fractions (1 1/3 cup).

The recipe scaling is based of the number of servings, in this case 4. So when I type 1024, the scaling number (multiplier) is 256.

So if my recipe calls for 2 teaspoons, based on the scaling number will generate 512 teaspoons. Then that number will get converted to 170 tablespoons + 2 teaspoons. I want it to go to a smaller unit of measure which will be 10 2/3 cups.

Here is the file in which i drew inspiration: http://www.chefs-resources.com/wp-content/uploads/Recipe_Scaling_Form_PScharf_v2.xltx the unprotect password is: recipe
there is a hidden sheet with the formulas

hop that helps

 

[Gorango]

And an example of how it works

http://www.chefs-resources.com/wp-content/uploads/Recipe_Scaling_Form_Example_PScharf_v2.xlsx

 

[oldbrewer]

If it doesn't make ANY sense to you I am flummoxed. I made up a pretend recipe that serves 15 and scaled it up so it serves 1024

It doesn't matter what units you use, kg lb cup ml

converting lb to cups is easy once you define the conversion factor

I multiplied all amounts by 1024 divide by 15

sorry I never download on here

 

[Gorango]

I understand the converting part. What I want is to have my employees not have to measure 170 tablespoons of an ingredient, I'd much rather have them measure 10 2/3 cups and I am having difficulty getting that on the spreadsheet. The website I got the original file was from Chef's Resources (I understand the reasoning to not downloading anything)

I am going to use excel screen shots since it seems my explanation is hard to follow, and for that I am sorry.


Here is the yield information as it appears on the sheet (Sheet name - Base Recipe):

	C	D	E	F	G	H	I	J	K	L	M

<tbody>
[TD="align: center"]7[/TD]
[TD="align: center"]Yield[/TD]
[TD="align: center"]Unit[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Servings[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Scale to[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]fl oz[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]1024[/TD]
[TD="bgcolor: #FFFFFF, align: right"]256[/TD]
[TD="bgcolor: #FFFFFF"]X[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

​

L8 is taking the amount of servings I need from K8 and dividing by the base servings in G8

Here is where I input my ingredients and measures. E through M are where the calculations happen. J is a plus sign to signify there's an addition measurement in K (Sheet name - Base Recipe):

	B	C	D	E	F	G	H	I	J	K	L
INGREDIENTS	AMT	UNIT	AMT	UNIT		AMT	UNIT
garlic cloves, minced	TBSP	TSP

<tbody>
[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]TSP[/TD]
[TD="align: right"]512[/TD]
[TD="align: right"]TSP[/TD]
[TD="align: right"][/TD]
[TD="align: center"]170[/TD]

[TD="align: center"]+[/TD]
[TD="align: center"]2.0[/TD]

</tbody>

Base Recipe​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E12[/TH]
[TD="align: left"]=C12*$L$8[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F12[/TH]
[TD="align: left"]=D12[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H12[/TH]
[TD="align: left"]=INDEX(rng12.1,MATCH(D12,rngMeasure,0))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I12[/TH]
[TD="align: left"]=INDEX(rngUnit2,MATCH(D12,rngUnit1,0))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]K12[/TH]
[TD="align: left"]=INDEX(rng12.2,MATCH(D12,rngMeasure,0))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]L12[/TH]
[TD="align: left"]=INDEX(rngUnit3,MATCH(D12,rngUnit1,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]rng12.1[/TH]
[TD="align: left"]=Formulas!$B$17:$B$28[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]rng12.2[/TH]
[TD="align: left"]=Formulas!$C$17:$C$28[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]rngMeasure[/TH]
[TD="align: left"]=Formulas!$A$17:$A$28[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]rngUnit1[/TH]
[TD="align: left"]=Table2[Unit1][/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]rngUnit2[/TH]
[TD="align: left"]=Table2[Unit2][/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]rngUnit3[/TH]
[TD="align: left"]=Table2[Unit3][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Here is the rngunits array as a picture since I couldnt get the html link to work here (Sheet name - Formulas):



And here is the rest of the information you all might need (Sheet name - Formulas):

	A	B	C

<tbody>
[TD="align: center"]16[/TD]
[TD="bgcolor: #DCE6F1"]MEAS[/TD]
[TD="bgcolor: #DCE6F1, align: right"]12.1[/TD]
[TD="bgcolor: #DCE6F1, align: right"]12.2[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #B8CCE4"]CUP[/TD]
[TD="bgcolor: #DCE6F1, align: right"]128[/TD]
[TD="align: right"]0.0[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #DCE6F1"]FL OZ[/TD]
[TD="bgcolor: #DCE6F1, align: right"]16[/TD]
[TD="align: right"]0.0[/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #B8CCE4"]GAL[/TD]
[TD="bgcolor: #DCE6F1, align: right"]512[/TD]
[TD="align: right"]0.0[/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #DCE6F1"]LB[/TD]
[TD="bgcolor: #DCE6F1, align: right"]512[/TD]
[TD="align: right"]0.0[/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #B8CCE4"]OZ[/TD]
[TD="bgcolor: #DCE6F1, align: right"]32[/TD]
[TD="align: right"]0.0[/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #DCE6F1"]PINT[/TD]
[TD="bgcolor: #DCE6F1, align: right"]256[/TD]
[TD="align: right"]0.0[/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #B8CCE4"]QT[/TD]
[TD="bgcolor: #DCE6F1, align: right"]128[/TD]
[TD="align: right"]0.0[/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #DCE6F1"]TBSP[/TD]
[TD="bgcolor: #DCE6F1, align: right"]32[/TD]
[TD="align: right"]0.0[/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #B8CCE4"]TSP[/TD]
[TD="bgcolor: #DCE6F1, align: right"]170[/TD]
[TD="align: right"]2.0[/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #DCE6F1"]EA[/TD]
[TD="bgcolor: #DCE6F1, align: right"]42[/TD]
[TD="align: right"]8.0[/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: #B8CCE4"]GRAM[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0[/TD]
[TD="align: right"]512.0[/TD]

[TD="align: center"]28[/TD]
[TD="bgcolor: #DCE6F1"]ML[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0[/TD]
[TD="align: right"]512.0[/TD]

</tbody>

Formulas​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B17[/TH]
[TD="align: left"]=INT('Base Recipe'!$E12/4)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C17[/TH]
[TD="align: left"]=(('Base Recipe'!$E12/4)-INT('Base Recipe'!$E12/4))*4[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B18[/TH]
[TD="align: left"]=INT('Base Recipe'!$E12/32)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C18[/TH]
[TD="align: left"]=(('Base Recipe'!$E12/32)-INT('Base Recipe'!$E12/32))*4[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B19[/TH]
[TD="align: left"]=INT('Base Recipe'!$E12)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C19[/TH]
[TD="align: left"]=(('Base Recipe'!$E12)-INT('Base Recipe'!$E12))*4[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B20[/TH]
[TD="align: left"]=INT('Base Recipe'!$E12)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C20[/TH]
[TD="align: left"]=(('Base Recipe'!$E12)-INT('Base Recipe'!$E12))*16[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B21[/TH]
[TD="align: left"]=INT('Base Recipe'!$E12/16)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C21[/TH]
[TD="align: left"]=(('Base Recipe'!$E12/16)-INT('Base Recipe'!$E12/16))*16[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B22[/TH]
[TD="align: left"]=INT('Base Recipe'!$E12/2)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C22[/TH]
[TD="align: left"]=(('Base Recipe'!$E12/2)-INT('Base Recipe'!$E12/2))*4[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B23[/TH]
[TD="align: left"]=INT('Base Recipe'!$E12/4)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C23[/TH]
[TD="align: left"]=(('Base Recipe'!$E12/4)-INT('Base Recipe'!$E12/4))*4[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B24[/TH]
[TD="align: left"]=INT('Base Recipe'!$E12/16)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C24[/TH]
[TD="align: left"]=(('Base Recipe'!$E12/16)-INT('Base Recipe'!$E12/16))*16[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B25[/TH]
[TD="align: left"]=INT('Base Recipe'!$E12/3)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C25[/TH]
[TD="align: left"]=(('Base Recipe'!$E12/3)-INT('Base Recipe'!$E12/3))*3[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B26[/TH]
[TD="align: left"]=INT('Base Recipe'!$E12/12)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C26[/TH]
[TD="align: left"]=(('Base Recipe'!$E12/12)-INT('Base Recipe'!$E12/12))*12[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B27[/TH]
[TD="align: left"]=INT('Base Recipe'!$E12/1000)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C27[/TH]
[TD="align: left"]=(('Base Recipe'!$E12/1000)-INT('Base Recipe'!$E12/1000))*1000[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B28[/TH]
[TD="align: left"]=INT('Base Recipe'!$E12/1000)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C28[/TH]
[TD="align: left"]=(('Base Recipe'!$E12/1000)-INT('Base Recipe'!$E12/1000))*1000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Hopefully that's not too much information or confusing and I want to thank you for your attempts to help me.

 

[oldbrewer]

sadly - it is too complex for me

if all conversions are defined eg cups to ounces, tablespoons to cups, I cannot see a problem in scaling up any recipe, using like if number of tablespoons > 5 convert to lbs

 

[Gorango]

Thank you for your time.

This makes me wonder if it is even possible to do what I want.

 

[oldbrewer]

taking teaspoons as an example if you scale up 12 times the answer can be in tablespoons, scale up 32 times, the answer can be in cups, scale up more than say 200 times the answer can be in lbs and ounces - you tell me the transition scale up figures and I can make a reference table that will sort out any recipe scaling.
This is straightforward for excel. If I have time later I will make a small example.

 

[Gorango]

Interesting. I'm going to hash that out.

For reference I only need to work in these units of measure: Teaspoons, Tablespoons, fluid ounces, cups, gallons, ounces, and pounds.

Thanks again.