Route Optimization Tips and Tricks with CDXZipStream

Our Microsoft Excel add-in, CDXZipStream, works with MapPoint to find the optimal order of stops on a driving route.  Just list the addresses in an Excel worksheet, and CDXZipStream will return the list in the order that achieves the shortest driving time.  For a quick overview, please see the video below:

or the YouTube version:  Route Optimization in Excel. The route optimization capability of MapPoint is a very powerful time (and money) -saving tool, but there are a couple of things you need to keep in mind when using this optimization function.

First (and this isn't too surprising), the quality of the returned data is dependent on the quality of the data you provide.  If the starting list of addresses contain errors, whether they be minor typos, incorrect zip codes, or non-existent street names, route optimization either can't proceed, or the software may make an educated guess concerning the address in question and optimize based on that guess.  Therefore, it's important to make sure that the optimized list of addresses really do represent the intended destinations on the route.  In the case of CDXZipStream, MapPoint may designate an address a "best match" indicating that an exact match wasn't found – these cases should be carefully reviewed.  Checking the accuracy of address data before optimization is also possible using CDXZipStream - please see the tutorial below for more informaiton:

or the YouTube version: Address Validation in Excel 

Another consideration when using MapPoint, is that the first and last addresses of a given route are assumed to be fixed.  In other words, the beginning and end points do not change and only the intermediate points are optimized.  This assumption applies to the vast majority of driving routes; for instance, a delivery truck begins and ends a route at a known warehouse location, or a sales rep visits clients starting from the place of employment or home address.  When providing the list of addresses to be optimized, please be sure the first and last addresses are selected appropriately.

One more thing to keep in mind when optimizing a route:  MapPoint always optimizes on the basis of minimizing driving time.  However, if you request directions for the optimized route, you do have the option of selecting the directions based on shortest distance, quickest time, or your specified road type driving preferences.  These do not affect the order of the stops, but do impact the actual driven route. When using CDXZipStream in Excel you can select road driving preferences by right-clicking on your worksheet, selecting the CDXRouteMP function, choosing Route Settings, then Road Type.  You can also specify in Route Settings other assumptions regarding fuel cost, driving speeds, and stopover times, that will impact calculation of the total trip duration and cost.  Fortunately, all the options available in MapPoint are also available through the CDXRouteMP function.

CDXZipStream is a simple, economical tool that can perform route optimization while the user works entirely within Microsoft Excel.  We hope this short list of hints help you reach your business goals (and driving destinations!) quickly and easily.

Add comment

Loading