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).

24 Comments

  1. 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. 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. 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?

  4. 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!

  5. 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.

    • Hi Martin, probably the link to the excel file is broken. I cannot download the file. I see great use of your spreadsheet in my test kitchen. Thanks

      • Thanks for notifying me Juan. It works on my side, but I will need to check up up how to fix this for everyone. I think it has to do with the MIME type. Perhaps you could try to download with a different browser?

  6. 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.

  7. 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

    • 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?

  8. 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

  9. 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

  10. 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.

  11. 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.

  12. Martin, thanks for your fantastic work on this! I was inspired after using it and took the liberty of converting it to a free web application.

    http://volum.io

    Any and all feedback is welcomed, this is certainly a work-in-progress. It’s also open source: https://github.com/greenham/volume2weight

    Planned features include allowing for conversion of multiple ingredients, importing from various formats (csv, hRecipe), and user recipe creation/storage/publishing, maybe a mobile app.

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.