report

Creating Top 10 Lists and League Tables in Excel 2007

Introduction

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.

Dynamic league table with labels created using MIN, MAX, SMALL and LARGE as well as  the INDEX and MATCH functions using Excel 2007.
Dynamic league table with labels created using MIN, MAX, SMALL and LARGE as well as the INDEX and MATCH functions using Excel 2007. | Source

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.

=MAX(<Range>)

Correct syntax for the MAX function in Excel 2007.
Correct syntax for the MAX function in Excel 2007. | Source

MIN works in exactly the same way

=MIN(<Range>)

By creating a formula for each column for both the MIN and MAX function, I end up with the following.

Example of results obtained using the MIN and MAX function in Excel 2007.
Example of results obtained using the MIN and MAX function in Excel 2007. | Source

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.

=LARGE(<RANGE>,1)

And ending with

=LARGE(<RANGE>,10)

This gives me a list as shown below

Example of the syntax of the LARGE function in Excel 2007.
Example of the syntax of the LARGE function in Excel 2007. | Source
Highest 10 values from a data range obtained using the LARGE function in Excel 2007.
Highest 10 values from a data range obtained using the LARGE function in Excel 2007. | Source

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.

=SMALL(<RANGE>,3)

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.

Syntax of a formula using the INDEX, MATCH and LARGE functions used to create a dynamic table showing the top ten items in a list in Excel 2007.
Syntax of a formula using the INDEX, MATCH and LARGE functions used to create a dynamic table showing the top ten items in a list in Excel 2007. | Source

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:

=MATCH(lookup_value,lookup_array,match_type)

  • 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:

=INDEX(array,row_num,column_num)

  • 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

Completed dynamic league table created using the INDEX, MATCH and LARGE functions in Excel 2007.
Completed dynamic league table created using the INDEX, MATCH and LARGE functions in Excel 2007. | Source

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.

Conclusion

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.

More by this Author


Comments 2 comments

Robbie C Wilson 3 years ago Author

Thanks for your kind comment Paul. I am glad that you liked the article. Good to hear that the screenshots were helpful and the examples useful. I love looking into the functions available in Excel and figuring out new and interesting ways of using them and then sharing that via HubPages.


Paul Maplesden profile image

Paul Maplesden 3 years ago from Asheville, NC

As a keen Excel user, I think this is a really good article. I like the way that you separated out the various functions, providing good examples with each. The use of screenshots was also very useful in summarising the key steps people need to take. A great tutorial for some often overlooked functions, great work.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article

    Menu

    Explore