Sorry to resurect this very old question but I was looking to do the same thing and could not find the answer anywhere. My missus is useless when it comes to multiplying a recipe from her books to cover the number of people she is cooking for so i created a spreadsheet for her to enter the number of people the recipe is aimed at and the number of people se is cooking for and calculate the weights. of course the recipies all use different measurements and I wanted to account for them all.
Here is what i ended up doing...
first format the column you want to enter the values into as plain text
format > number > plain text
This will allow a number without units to work with the following regex functions
add a few columns beside the column you want to enter your value into...
in these columns add the formulas -
column C. =VALUE(REGEXREPLACE(B7,"[^[:digit:].]", ""))
column D. =REGEXREPLACE(B7,"[^[:alpha:]]", "")
this will split the entered measurement into its value and unit components.
now create a conversion table on a separate sheet that contains the following -
unit |
conversion rate |
base unit |
base increment |
multiplier |
g |
1 |
g |
kg |
1000 |
kg |
1000 |
g |
kg |
1000 |
lb |
454 |
g |
kg |
1000 |
lbs |
454 |
g |
kg |
1000 |
oz |
28 |
g |
kg |
1000 |
ml |
1 |
ml |
L |
1000 |
L |
1000 |
ml |
L |
1000 |
pt |
568 |
ml |
L |
1000 |
qt |
1137 |
ml |
L |
1000 |
floz |
29 |
ml |
L |
1000 |
tsp |
1 |
tsp |
tbsp |
3 |
tbsp |
1 |
tbsp |
tbsp |
1 |
... |
|
|
|
|
add whatever conversions you want on here
back on the main sheet continue adding columns to do the lookups
column E. =INDEX(Sheet2!$A$2:$C$99,MATCH(D7,Sheet2!$A$2:$A$99,0),3)
this gets the base unit
column F. =if(E7="",1,INDEX(Sheet2!$A$2:$C$99,MATCH(D7,Sheet2!$A$2:$A$99,0),2))
gets the conversion rate (defaults to 1 if the unit entered is unknown)
column G. =F7*C7
Gets the total base qty for the recipe
column H. =round((G7/$K$3)*$K$4)
adjust for the number of people being served (k3 holds the number servings on the recipe, k4 holds the number of servings required)
column I. =if(E7="","",INDEX(Sheet2!$A$2:$E$99,MATCH(D8,Sheet2!$A$2:$A$99,0),4))
column J. =if(E7="",1,INDEX(Sheet2!$A$2:$E$99,MATCH(D7,Sheet2!$A$2:$A$99,0),5))
Gets the values needed to convert to the highest denomination of the measurement
column K. =if(H12/J12 >= 1,H12/J12 & " " & if(I12="",D12,I12), H12 & " " & E12)
this is the actual value we have been looking for. It checks if the conversion to the higher denomination is greater or equal to 1. If so it uses the higher denomination - otherwise it uses the base denomination.
Hide columns C - J and this is what you get -
Recipe Multiplier |
|
|
|
|
|
recipe servings |
|
4 |
req servings |
|
6 |
|
|
|
ingredient |
recipe qty |
total qty |
eggs |
2 |
3 |
sugar |
6 oz |
252 g |
milk |
10 fl oz |
435 ml |
cinnamon |
1 tsp |
2 tsp |
flour |
750 g |
1.125 kg |