CDXZipStream supports a variety of distance formulas including CDXDistance, CDXRouteMP and CDXDistance2WP.  Each is optimized for different situations and involves varying times to calculate.  

CDXDistance is used to calculate straight-line distance between zip codes.  You simply reference two zip codes and the CDXDistance formula returns the distance in either miles or kilometers.  The custom formula calls our custom database to return latitude and longitude for the zip code, and the distance calculation is performed based on this data.  It is relatively fast and should be used where the central location of a zip code can be used to approximate distance between locations.

If you need driving distance or time you will need to use CDXRouteMP, which works in conjunction with the desktop version of Microsoft MapPoint.  The calculation times for this are relatively slow at about 1 second per route, with more complex routes taking more time.  If you are going to make thousands of calculations you should dedicate your PC to this during off hours or run this on a separate machine.

The fastest calculation is CDXDistance2WP, which uses latitude/longitude pairs to perform straight line distance calculation. You can geocode a list of addresses and then use this function to calculate exact distances.  We recommend this when trying to analyze long address lists.

A typical task may require calculating driving time or distance for a large matrix of addresses or zip codes.  For instance, it may be necessary to determine which customers in a list of thousands are within a one hour driving time of multiple store locations.  The best way to handle this is a two-step process.  First, we can get the latitude and longitude of each customer address and use CDXDistance2WP to calculate the straight-line distance from each store.  Then we can filter this list for customers that are within 100 miles of each store, and use CDXRouteMP to get the exact driving time for this smaller list.  Using a combination of distance calculations in this case allows for fairly fast analysis of very large data sets, without sacrificing accuracy

Custom functions, otherwise known as user-defined functions or UDF's, are an extremely useful but very often untapped resource in Microsoft Excel.  Just like standard functions SUM or AVERAGE, they are used in cells formulas to perform a variety tasks, some quite complex. The difference is that UDF's can be custom designed by and for you.  Sounds hard?  There is a bit of a learning curve, but if you are performing repetitive calculations throughout a worksheet, it can save you lots of time and frustration, and once you get your feet wet you will come to appreciate how easy it is to apply UDF's in  many situations.

For example, our Excel add-in CDXZipStream uses a custom function (designed by us!) to import data from an Access database into a worksheet cell.  The custom function to import the median age for the zip code 08034 looks like this:

  =CDXZipCode("08034","MedianAge")

Don't be too concerned about the fact that this is in a worksheet formula.  There is no math involved whatsoever.  The formula is simply calling a function that has a specified task to perform, and the parameters within the parentheses are communicating the details of how that task gets done.  In this case the custom function CDXZipCode is being called, and we're telling it to get age data for a particular zip code.  If we need data like this for a lot of zip codes, it is easy to see how using a custom function can be a great alternative to manually looking up data the old-fashioned way.

So what about that learning curve?  Custom functions are created using the programming language Visual Basic, which is relatively easy to learn and use. Visual Basic for Applications, otherwise known as VBA, is specific to MS Office applications like Excel.  The cool thing about VBA is that you can "record" a series of actions within your worksheet, and then use the resulting code as the basis of your custom function -  you don't have to create code from scratch, although you may have to modify the recorded code somewhat so it can be used in all situations.  There are also numerous examples of VBA code within the Help function of the Visual Basic editor in Excel, and even more on the Web and other resources.  We guarantee, once you create your own UDF with Visual Basic and see how simple (and powerful) it is, you'll soon envision a whole range of functions that can be used within your worksheets.  To get started, here are some great resources:

A Microsoft tutorial for creating UDF's

Step-by-step instructions for macros and UDF's from Marshall Business School, USC

A basic, simple example from ExcelTip.com

Some nice examples of UDF's (and their VBA code) from FontStuff.com

VBA programming reference books from John Walkenbach

And last but not least, one of our YouTube videos showing CDXZipStream custom functions in action:

Zip Code Finder in Excel