Creating Top 10 Lists and League Tables in Excel 2007
Welcome to my latest hub on Excel 2007. Today, I am going to look at the MIN and MAX functions alongside the SMALL and LARGE functions and how they can be used on your data to provide you with the largest and smallest numbers in your data. Using these functions alongside INDEX and MATCH allows you can create top 10 lists and league tables.
In addition, I will look at the INDEX and MATCH functions and how they can extract a label to go along with the data gathered using MIN, MAX, SMALL and LARGE to create a dynamic table with the label (in my case Hub name) and value (in this instance, total number of hits).
- MIN and MAX provide you with the smallest and the biggest number in your data range.
- SMALL and LARGE perform a similar function; however, they allow an argument which can for example allow you to determine the 3 largest numbers in your data, or the second smallest.
Working with the MIN and MAX functions
I have a number of hubs I have created over time and would like to know the minimum and maximum values of my Hub Score, my Hub Views over different periods of time etc. I can easily use MIN and MAX to display these.
MIN and MAX are quite straightforward to use, simply start as with all formulas or functions with an = followed by MAX and the range of cells you want to check.
MIN works in exactly the same way
By creating a formula for each column for both the MIN and MAX function, I end up with the following.
Working with the SMALL and LARGE functions
SMALL and LARGE are very useful if you want to create a league table, or to find a specific value, for example the fourth smallest value in your data range.
For my example above, I want to know the 10 highest values for total overall hits for my published hubs. To do this I use the LARGE function in ten formulas, starting with 1, 2, 3 and ending with 10 as below.
And ending with
This gives me a list as shown below
Similarly, working with SMALL, could give me a list of the ten hubs with the lowest totals. If I wanted to know the third smallest value for total hub views of all my hubs, I would use a formula as below, with the range being the column containing the total views.
Limitations of MIN, MAX, SMALL and LARGE functions
If you look at my league table of the ten hubs with the highest number of overall hits above once more, you will notice the limitations that these functions have. While they are fantastic at returning the smallest or largest values in a range, or telling you the third smallest value in a range, that is all they tell you.
For my league table it would be very helpful to know which hubs they were as well as their totals.
In part two of this hub we are going to explore a way to get Excel 2007 to provide us with the names of my top performing hubs automatically to go along with their total hits. To do this we are going to use three functions, one of which we have already used INDEX, MATCH andLARGE
Using the INDEX and MATCH functions alongside LARGE
To create a league table that not only contains the total number of hits for my ten most popular hubs but also the names of those hubs, we have to use two new functions alongside LARGE. These are the INDEX and MATCH functions.
What we are doing in essence is
- Firstly we ask Excel to find the largest value in the column for total hits using LARGE
- We then ask Excel to use MATCH to find the position of that cell in the same column
- INDEX then looks for the cell that is adjacent to that cell in the column that contains the hub names
Voila, it will construct our league table
OK now onto the formula itself shown in the figure below.
OK, so again starting with LARGE, it simply looks for the number one largest value in the column that contains the total hub views exactly as we did in our earlier example.
For MATCH, the syntax is as follows:
- The lookup_value was provided by the result given to us by LARGE
- The lookup_array is once again the column that contains the total hub views and
- The match type is 0 which tells MATCH to look for the number that is exactly equal to the result given to us by LARGE
For INDEX, the syntax is as follows:
- The array is the column containing the hub names
- The row_num and column_num is provided by MATCH
So, our two formulas together,
=INDEX('hubs(1)'!A3:A47,MATCH(LARGE(('hubs(1)'!F3:F47),1),'hubs(1)'!F3:F47)) and =LARGE('hubs(1)'!F3:F47,1) provide us with the following table
This is exactly what we wanted to achieve which is brilliant. To create each row, we just increased the value in the LARGE part of the formula from 1 to 2 and so on
=INDEX('hubs(1)'!A3:A47,MATCH(LARGE(('hubs(1)'!F3:F47),2),'hubs(1)'!F3:F47)) and =LARGE('hubs(1)'!F3:F47,2)
Alternatives to INDEX, MATCH and LARGE
Before investigating the possibility of using INDEX, MATCH and LARGE in combination, I investigated the possibility of using VLOOKUP.
VLOOKUP is an immensely powerful function, however, it is unable to search a column to the left of the column you are interested in, which is the case in this example. Rather than move the data around each time I extract it into this spreadsheet, I discarded VLOOKUP in this instance.
I have another hub which looks into VLOOKUP in greater detail. I used it to auto-fill prices for various music media and also to auto-fill a customer’s address based on their customer number. My hub for VLOOKUP can be found here.
A pivot table would have worked perfectly in this instance, but not with the MIN and MAX, LARGE and SMALL functions I had already started to use. In fact, I already have such a table but I wanted to follow through with these functions.
Pivot tables are also very powerful, allowing you to create a table from your data and then change it at will. If you decide that you want your pivot table to provide you the hubs with the lowest Hub Score and then change your mind to the hubs with the top ten hits for the last seven days, this can be done easily and quickly. What’s more, pivot tables are dynamic, when the data changes they can be instantly refreshed to reflect that.
I also have a hub that goes into detail on creating, using and configuring pivot tables which can be found here.
I hope that you have enjoyed this hub on the MIN, MAX, LARGE, SMALL, INDEX and MATCH functions. We explored MIN, MAX, LARGE and SMALL in detail before exploring how INDEX and MATCH could make those initial functions even more useful.
We started with the first four functions returning a number (for example the largest number of total hits) and using INDEX and MATCH we were able to return the actual name of that hub to create a dynamic table league table.
As is often the case in Excel 2007 and why it is such an interesting application to learn and also why it is so powerful is that you can take some seemingly un-related functions and build something using them that is both powerful and very useful. Many thanks for reading and if you have any comments, please feel free to leave them below.