Route Optimization Using Microsoft Excel and MapPoint

Route optimization involves determining the best order of stopping points along a driving route, in order to achieve the shortest driving time or distance. Optimizing routes (for product deliveries, service and sales calls, mobile health care, etc.) can result in significant savings in time, money, and fuel. Fortunately, it is possible to look beyond traditional, expensive route optimization software and use Microsoft Excel custom functions to perform the same calculations.

microsoft security essentials

Microsoft Excel custom functions, also referred to as UDFs or "user-defined functions" work within a worksheet cell formula to perform a wide variety of tasks, and are used just like standard Excel functions such as LOOKUP or AVERAGE. To perform route optimization for a list of addresses in Excel, a custom function works with Microsoft MapPoint to automatically return the re-ordered, optimized list directly back to the worksheet. MapPoint is a route planning and mapping software that integrates with Microsoft Office products such as Excel. Through the use of custom functions, all interactions with MapPoint happen in the background; there is no need to learn a new application because you only need to work within the familiar Excel environment.

MICROSOFT SECURITY

Let's say you have a list of addresses in Excel that represent a daily route of customer service calls. A custom function to determine the optimal driving order would be used in an Excel formula like this: "= CustomFunction (AddressList)", where AddressList is the worksheet cell range containing the addresses. For example, the formula "= CustomFunction (A1:A15)", returns an optimized list of all the addresses in cells A1 through A15. Microsoft MapPoint (running in the background) determines the best order based on the shortest driving time, assuming that the first and last addresses are fixed and do not change in the order.

Microsoft Excel, as a spreadsheet application, is especially well suited to handle large sets of data, and in this case can perform route optimization for multiple sets of routes typical of a delivery service and other business-related driving.

It is also possible to return to the worksheet other results of the route optimization calculation, such as the total duration of the trip (including stopovers at each address), fuel cost, driving time and distance, a map of the route, and even step-by-step driving instructions.

Route optimization is just one example of how custom functions in Excel can utilize the power of other applications, such as Microsoft MapPoint, while allowing the user to work within the familiar Excel environment.

Route Optimization Using Microsoft Excel and MapPoint

The author of this article, Betty Hughes, helped develop CDXZipStream™, an Excel add in that provides zip code data and demographic information, and performs address-based calculations, such as route optimization, using custom functions. For more information please visit us at CDXZipStream.com or watch our YouTube video Route Optimization in Excel.

MICROSOFT SECURITY