How to get started with indexing using Excel
Like almost everyone in the engineering field, at CEA we use Excel extensively because it’s powerful, affordable and reduces the time it takes to analyse problems. Our favourite function – and the one we tend to use more than others – is the index function. It allows us to take the values out of a matrix and put them in tabular form – meaning we can combine the advantages of both formats.
Starting with a matrix
A matrix comes into its own when we’re thinking about how different entities interact. In the example below, we are looking at a very basic matrix that shows the distribution of material from cuts to fills. In reality, our matrices are normally far more complicated than this because we tend to add in different material types, chainages and the like – but you can get the idea.
Adding indexing
Once you’ve built your matrix, and confirmed that you’ve moved the cut to fill in the most logical sequence, it’s time to tabulate it so you can look at each individual haul. This is where indexing offers real value. The screenshot below explains it best but, in summary, we write a series of formulas that return the value in each segment of the matrix. This allows us to more easily manipulate data for reports.
Indexing tips
Here are a few tips that make life easier when it comes to user-friendly indexing:
Add the row and column numbers on the array. In the above example, they are in column A and Row 1. These make life easy when you are trying to work out which part of the matrix you need to assess.
Use some basic formulas to assess row and column numbers for indexing (see columns I though N in the above example). This means you can easily ‘drag’ the index formula down, instead of trying to manually change each cell.
Once you have the values in each segment, it’s easy to filter and only look at the ‘non-zero’ values. Straight away, you have a list of hauls that can be used to manage the haulage process.
At CEA, we take this process further, like adding in haulage lengths, hauling units, production rates and more. The key is, once you master the basics of indexing, it opens up a massive range of analysis options for running checks and even alternative scenarios in a fraction of the time it would take to do it manually.
We hope this gives you some ideas for your own project, and some tips for the next time you need to distil the contents of a matrix into a list. For a simple start, you can download the example file we have used here. While the example may seem pretty vanilla, our team used this same idea on a project that had 90 cut and fill locations, with up to six material types in each location. It meant we could provide our client with accurate haulage estimates and run ‘what if’ scenarios in a fraction of the time traditional calculation methods would have taken.
