Why bartenders may soon love Excel as much as accountants

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:

=ConvDash2Tsp(50)

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:

=ConvTsp2Ml(ConvDash2Tsp(50))

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.

Function ConvOz2L(oz)
'Converts US fluid ounces to liters
ConvOz2L = oz / 33.8140226
End Function

Function ConvL2Oz(L)
'Converts Metric Liters to US Ounces
ConvL2Oz = L * 33.8140226
End Function

Function ConvOz2ml(oz)
'Converts US Ounces to Milliliters
ConvOz2ml = oz / 0.0338140226
End Function

Function ConvTsp2Oz(tsp)
'Converts teaspoons to ounces
ConvTsp2Oz = tsp / 6
End Function

Function ConvTblsp2Oz(tblsp)
'Converts tablespoons to ounces
ConvTblsp2Oz = tblsp / 2
End Function

Function ConvDash2Tsp(dash)
'Converts dashes into teaspoons assumes 8 dashes in a teaspoon
ConvDash2Tsp = dash / 8
End Function

Function ConvTsp2ml(tsp)
'Converts teaspoons to milliliters
ConvTsp2ml = tsp * 4.9289216
End Function

Function ConvMl2Tsp(ml)
'Converts milliliters to teaspoons
ConvMl2Tsp = ml / 4.9289216
End Function

Function ConvCups2ml(cups)
'Converts US cups to milliliters
ConvCups2ml = cups * 236.58824
End Function

Function ConvMl2cups(ml)
'Converts milliliters to US cups
ConvMl2cups = ml / 236.58824
End Function

Function ConvQuarts2L(quarts)
'Converts quarts to liters
ConvQuarts2L = quarts * 0.94635295
End Function

Function ConvL2Quarts(L)
'Converts liters to quarts
ConvL2Quarts = L / 0.94635295
End Function

Function ConvL2Gal(L)
'Converts liters to gallons
ConvL2Gal = L * 0.26417205
End Function

Function ConvGal2L(gal)
'Converts gallons to liters
ConvGal2L = gal / 0.26417205
End Function

Function ConvL2Cup(L)
'Converts liters to cups
ConvL2Cup = L * 4.2267528
End Function

Function ConvCup2L(cup)
ConvCup2L = cup / 4.2267528
End Function