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 lead to 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 an address list in Excel, a custom function works with Microsoft MapPoint to automatically return the reordered and optimized list directly to the worksheet. MapPoint is route planning and mapping software that integrates with Microsoft Office products like Excel. By using custom functions, all interactions with MapPoint happen in the background; There’s no need to learn a new application because you just need to work in the familiar Excel environment.
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 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 driving time, assuming that the first and last addresses are fixed and do not change in order.
Microsoft Excel, as a spreadsheet application, is especially 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 driving related business. .
It is also possible to return to the spreadsheet other results of the route optimization calculation, such as total trip time (including stopovers in each direction), fuel cost, driving time and distance, a map of the route and even the pass. -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.