UPDATE (3/28/11): I've been thinking about this post for awhile, unable to believe that someone else hadn't already thought of the obvious. Did a little research this morning and realized all my work was in vein (but I am happy about it). Excel does have an =CONVERT(number,from_unit,to_unit) function built in and is much easier than trying to manage the macros necessary to build your own solution. Just about every possible unit series(distance, length, weight, volume) is included and the friendly drop-downs even guide you through it.
Excel has been a favorite among accountants pretty much since it was released. Very few Microsoft products are loved by their users the way Excel is loved by the accounting community. Most of our institutions are held together by monster spreadsheets with ridiculously complex formulas that only one or two people can actually decipher. And that's okay. Excel is a good product, but I recently found myself wanting for more.
This post, quite different from most, is not targeted at the people who keep mail and directory systems operating every day. This post is targeted at bartenders seeking help with reducing or expanding their recipes for barrel aging, rebottling, whatever the current trend happens to be.
One of my hobbies is the study of cocktails: their history, chemistry, creation, distillation, etc. Recently, I've been in the habit of pre-bottling some of my favorite cocktails, which involves mixing large batches. Of course, most cocktail recipes call for an ounce here or a dash there, but present quite the challenge when combining 750ml bottles of this and that. Reductions were even more challenging. How do I reduce another bartender's recipe, measured in metric bottles of x and quarts of y, such that it fits into a single 375ml bottle for testing and tasting. So, I turned to Excel to help me with the math.
To facilitate all the conversions, I created a series of functions in Excel. These functions are snippets of code that work just like Excel's built-in functions. So, let's say you have a recipe that calls for a dash of bitters. You want to make 50 of these cocktails ahead of time. How much should you pour into the mixer? The formula in Excel would be written as follows:
And the cell will display 6.25. And, just like standard Excel formulas, these functions can be compounded. So, if you want to see dashes translated to milliliters, you change the cell to:
where the inner formula's result is passed to the outer formula. The result of the second formula tells us that 50 dashes equals 30.81 milliliters.
It's unfortunate that for all the formulas offered by Excel, basic conversion between US and metric units were never considered. I've put together a list of a few of them, mostly pertaining to volume, but you can certainly use these as you see fit and create some of your own using the basic template.
The following code can be pasted into a new Macro window in Excel to take advantage of some of these conversion formulas.
'Converts US fluid ounces to liters
ConvOz2L = oz / 33.8140226
'Converts Metric Liters to US Ounces
ConvL2Oz = L * 33.8140226
'Converts US Ounces to Milliliters
ConvOz2ml = oz / 0.0338140226
'Converts teaspoons to ounces
ConvTsp2Oz = tsp / 6
'Converts tablespoons to ounces
ConvTblsp2Oz = tblsp / 2
'Converts dashes into teaspoons assumes 8 dashes in a teaspoon
ConvDash2Tsp = dash / 8
'Converts teaspoons to milliliters
ConvTsp2ml = tsp * 4.9289216
'Converts milliliters to teaspoons
ConvMl2Tsp = ml / 4.9289216
'Converts US cups to milliliters
ConvCups2ml = cups * 236.58824
'Converts milliliters to US cups
ConvMl2cups = ml / 236.58824
'Converts quarts to liters
ConvQuarts2L = quarts * 0.94635295
'Converts liters to quarts
ConvL2Quarts = L / 0.94635295
'Converts liters to gallons
ConvL2Gal = L * 0.26417205
'Converts gallons to liters
ConvGal2L = gal / 0.26417205
'Converts liters to cups
ConvL2Cup = L * 4.2267528
ConvCup2L = cup / 4.2267528