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?
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.)
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
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.
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
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).
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:
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:
Excellent tool! Thank you.
By the way, I noticed a formula error in column X.
The correct formula, starting at row 4:
Really nice. Someone should make an App out of it…
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?
Awesome! Thanks for this wonderful tool!
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!
Steve: Calculator updated with about 70 extra entries from the USDA database.
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?
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.
Good to hear that the calculator is being used!
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.
I see your point. The MD5 checksum of volume-weight-conversion-v2.xlsm is “82e526f2302096746ba6de7c9510ce5a”.
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
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?
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.
Sorry – I don’t have OSX and am not able to troubleshoot this. Perhaps other readers can help?
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.
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:
The numbers adaptation of v.2 can be found at:
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.
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.
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.
[…] on the Volume-to-weight calculator spreadsheet by Martin Lersch, who runs the fantastic Khymos […]
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.
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.