We will start by mapping all the different routes with their respective distances.įrom node A we can move to B and C, over a distance of 12 and 10.Īnd following the same rules, we can map the full network: Let’s create a new sheet where we will work out the shortest path for this particular network. Therefore, we need a generic solution that we can apply for a network of any scale.ĭownload the exercise file, which contains the network we will solve: In a small dataset it is relatively easy to work out the shortest path without an algorithm simply by calculating all different possibilities by hand, but as the network grows this will become nearly impossible to do manually. These are then combined to represent an approximated time for moving between locations (nodes). It takes into count things such as the physical distance, traffic, condition of the roads, and speed limits. For example, when a navigator plans your route, the route is calculated using a very similar principle. In reality, the distance between nodes is rarely pure physical distance nor a single number, but a combination of different variables that contribute to a number which represents the resources used to get from one node to another. Here is a simple network, where we can easily work out that the minimal distance from A to F is achieved by using node C -> E -> D, over a distance of 20. In addition, it is a brilliant puzzle to improve your computational thinking! Just as the name states, the objective in the shortest path problem is to get from start to goal by minimizing the distance. It is used for example in logistical problem solving, project management, and routing – to only mention a few. The shortest path problem is a fundamental optimization problem with a massive range of applications. In today’s post, you will learn how you can use Excel for solving the shortest path problem.

Does r have an excel solver function