A good way to calculate a multi-stop route is to put all stops along the route, including the origin and destination, in the same column.
In the example below:
- The origin is in cell B3, the first stop is in B4, the second stop in B5, etc.
- The Miles formula was entered in cell C4 to get the mileage from 08540 to Baltimore, MD; i.e. =Miles(B3,B4). This formula was then dragged from C4 down the length of the stop list to obtain leg mileages. (To replicate a formula down a column: Click the cell that has the formula; rest your cursor in the lower-right corner so that it turns into a plus sign (+); and click and drag down, up, or across the cells to which you want to apply the formula.)
- Total mileage was added at the bottom using the Excel function =SUM(C4:C9) to add up all miles in the Mileage column.
Using the same principle as multi-stop routing, Hub mileage (mileage from one “hub” location to multiple destinations) can be derived by entering the hub location at the top of a column and placing multiple destinations under it in the same column.
Simply set the origin in the =Miles formula in each cell to the stop at the top of the list; in the example below, to get miles from 08540 to Atlanta,GA, cell C6 contains the formula =Miles(B3,B6).