Route optimization involves determining the best order of stopping points along a driving route, in order to achieve the shortest driving time or distance. Route optimization (for product deliveries, sales and service calls, mobile healthcare, etc.) can result in significant savings in time, money and fuel. Fortunately, it’s possible to look beyond expensive traditional route optimization software and use Microsoft Excel’s custom functions to perform the same calculations.
Microsoft Excel custom functions, also called UDFs or “user-defined functions,” work within a spreadsheet 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 reordered and optimized list directly to the worksheet. MapPoint is mapping and route planning software that integrates with Microsoft Office products such as Excel. Through the use of custom functions, all interactions with MapPoint occur in the background; there is no need to learn a new application because you only need to work in the familiar environment of Excel.
Let’s say you have a list of addresses in Excel that represent a daily route of customer service calls. A custom function would be used to determine the optimal driving order in an Excel formula like this: “=CustomFunction(AddressList)”, where AddressList is the range of cells in the spreadsheet that contains the addresses. For example, the formula “=CustomFunction(A1:A15)”, returns an optimized list of all addresses in cells A1 through A15. Microsoft MapPoint (running in the background) determines the best order based on the shortest drive time, assuming the first and last addresses are fixed and do not change in order.
Microsoft Excel, as a spreadsheet application, is particularly well suited to handling large data sets 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 in each direction), the cost of fuel, the driving time and distance, a map of the route and even the step. -Step-by-step driving instructions.
Route optimization is just one example of how custom functions in Excel can harness the power of other applications, such as Microsoft MapPoint, while allowing the user to work within the familiar Excel environment.