Volume-to-weight calculator for the kitchen

glasbake-measuring-cup

Vintage Glasbake Measuring Cup by Gerrilynn Nunley

Despite the fact that the U.S. Metric Association have advocated metrication for nearly 100 years, many cookbooks still use US customary weights and volume measures. When following a recipe calling for teaspoons, tablespoons, fluid ounces, cups, pints, quarts or even gallons, I’ve often found myself using conversion websites such as Convert-me, picking ingredients from a list and entering the amount and unit. This works OK for single ingredients, but is less practical when converting a complete recipe. I therefore made a calculator to convert volumetric units to grams based on densities of a range of common ingredients. It has greatly simplified the task for me, and perhaps you’ll find it useful as well?

Download calculator
volume-weight-conversion-v2.xlsm (Excel 2007 file, 82 KB) – UPDATED!
(Please note that the autocomplete function requires that you enable the macro. If you experienced problems with the xlsm file being downloaded in zip format, please try again now. A small server update has been implemented to fix the problem.)

volume-weight-calculator

Features
Includes densities of about 200 275 ingredients.
Easy entering of data with autocomplete function and drop-down list.
Dynamic rounding of results to yield more realistic recipes (“120 g” water makes more sense than “118.29 g” which would be a more exact, but less practical conversion of 1/2 cup).
Optional scaling of the recipe

Allowed units
US volume units: t, T, floz, c, pt, qt, gal
US weight units: oz, lb
Metric volume units: ml, dl, l
Metric weight units: g, kg
For eggs, egg whites, egg yolks and a stick of butter the unit field is left blank. Average weights of Large and Medium sized eggs, whites and yolks are included.

Navigation
Use tab or left/right arrow to navigate in/out of ingredients
Autocomplete feature in ingredient field requires macros to be enabled
Use up/down arrows to navigate up/down in drop down list of ingredients

References
Densities mainly taken from rec.food.cooking’s FAQ and conversion file (note that this is the same source as Convert me is built upon) and USDA National Nutrient Database.
Some additional data from http://www.aqua-calc.com (which apparently is also based on the USDA database)
Some densities added/corrected by Martin Lersch (in particular flour densities in the rec.food.cooking seem to be off).

Tags: , , , , , , , , , , , , , , ,
Filed under: tips & tricks

Comments

  1. Steve Dunham Says:

    If you’re looking for more data, the USDA nutrient database has “household weights” data for a lot of items: e.g. a pat of butter, a clove of garlic, a cup of garlic, a cup of chopped onion (160g), a cup of sliced onion (115g).

    It looks like this database was the source for aqua-calc.com, but they don’t seem to be exposing all of the weight data via their web UI.

    The raw flat file data can be downloaded here:

    http://www.ars.usda.gov/Services/docs.htm?docid=23634

    They provide flat files, an access database, and an “abbreviated” single-table version (text or excel) that includes a subset of nutrients and the first three weight entries for each item.

    If you prefer, a copy of the full data that I have imported into an sqlite3 database can be found here:

    https://www.dropbox.com/s/wymdbxlf9y6ee5m/sr26.sqlite

  2. Brandon Says:

    Excellent tool! Thank you.

    By the way, I noticed a formula error in column X.

    The correct formula, starting at row 4:

    =$R4*X$2
    =$R5*X$2
    .
    .
    .
    etc.

  3. isaia panduri Says:

    Really nice. Someone should make an App out of it…

  4. Martin Lersch Says:

    Steve: Wow – a great resource. I’ll see how I can incorporate some of the data.

    Brandon: A good observation, but no – this is intentional and the formula is correct! The reason for this is that oz (ounce) is a unit of mass, so 1 oz = 28.3 g regardless of the density. Note however that floz (fluid ounce) which is a unit of volume does take density into consideration.

    isaia: Feel free to do that! But typing ingredients/amounts on a table/phone is so cumbersome that I’d personally prefer a PC with a keyboard. Then I could also print out the results and bring that into the kitchen. But maybe it’s only me being old fashined?

  5. Nuria Says:

    Awesome! Thanks for this wonderful tool!

  6. Michael Laiskonis Says:

    Martin – this is amazing, and a resource I will use over the hastily assembled conversions I’ve worked out myself over the years. As a chef who is regrettably forced to express my own metric recipes in volume measurements, I would love to see someone create a ‘metric to volume’ calculator to save even more time!

  7. Martin Lersch Says:

    Steve: Calculator updated with about 70 extra entries from the USDA database.

  8. Martin Lersch Says:

    Some may have experienced the .xlsm file turning into a .zip file when downloading (see further description of problem here).

    I’ve done some server updates so the problem should be resolved now. Please retry the download now.

  9. jill farrimond Says:

    This is a beautiful thing, no more trying to measure tablespoons of butter. Easy recipe scaling too, thank you so much for creating and sharing this.

  10. Martin Lersch Says:

    Good to hear that the calculator is being used!

  11. Mark Preston Says:

    My Linux “Calc” warns of potential viri or malware as POSSIBLY being present. I’m certain that this is not the case here. (I’m disregarding man-in-the-middle attacks). None the less, it would be nice if a checksum were available. Towards the bottom of this: https://en.wikipedia.org/wiki/Checksum are a number of free applets for MS, MAC and Linux/Unix. Most free.

  12. Martin Lersch Says:

    I see your point. The MD5 checksum of volume-weight-conversion-v2.xlsm is “82e526f2302096746ba6de7c9510ce5a”.

  13. Nick Caffrey Says:

    Hi, Martin,
    Any hope of incorporating imperial measures?
    I’m using this for a cookery course for the Department of Education of Ireland. We mostly use imperial.
    I’ll try and edit it myself if I can get some spare time!
    Regards and thanks
    Nick

  14. Tim Ong Says:

    Hi there,

    I am trying to access the file on OSX. Anyone experiencing any troubles trying to run the file on Excel for Mac?

    It takes me to VB and trys to debug some of the code.

    Any ideas?

    Thanks,

    Tim

  15. Martin Lersch Says:

    Sorry – I don’t have OSX and am not able to troubleshoot this. Perhaps other readers can help?

  16. Martin Lersch Says:

    Sure – which measures should I include? Wikipedia suggests these:

    Imperial gallon = 4,546.09 mL
    Quart = 1,136.5225 mL
    Pints = 568.26125 mL
    Fluid ounce = 28.4130625 mL

    Are these sufficient?

  17. Nick Caffrey Says:

    Hi, Martin,
    Thank you for your reply.
    Yes, those measures would be good together with the imperial teaspoon (tsp) and the imperial tablespoon (tbsp).
    It’s a really good resource; I’ve (slightly) amended it and used it in the classroom to calculate cost prices for recipes. The ‘scale’ button is a really useful idea.

    Regards,
    Nick Caffrey

  18. Steve Dunham Says:

    I don’t have excel for OSX, so I can’t debug that issue.

    I found that Numbers.app doesn’t like the formula in column E (it doesn’t support the “Array” stuff). If you replace the column E formula with the one shown below and use tsp/tbsp instead of t/T (the lookup is case-insensitive), it will work. (The clear button and autocomplete do not work, however.)

    Show column E, place this in E7, and copy/drag E7 into the rest of the E cells:

    =B7*VLOOKUP(D7,$P$4:$AD$278,IF(C7=”",2,MATCH(C7,$P$1:$AD$1,0)),FALSE)

    The numbers adaptation of v.2 can be found at:

    https://www.dropbox.com/s/89xjoh6d7a3r4it/volume-weight-conversion-v2.numbers

    This will work on the iPad version of Numbers, too, but you have to type an exact match for the ingredient without the help of autocomplete.

    OpenOffice on OSX doesn’t like the IFERROR() function, but it does handle the array formula in column E. (I didn’t test the autocomplete.)

    Microsoft is now making Office Online available for free now at https://www.office.com/start/default.aspx – The spreadsheet and autocomplete work on OSX/Chrome, but it the “Clear” button isn’t supported.

  19. Martin Lersch Says:

    Steve: Thanks for the update. The programing (using VB elements on top of a drop box) is not very nice… I’m glad you’ve found a workaround for the Numbers app.

  20. Joel Hodges Says:

    Fantastic, I can’t thank you enough! I’m trying to roll this over to Google Docs so that I can use it on our Chromebook in the kitchen.

Leave a Reply