This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

Creating a top 10 using formulas

Create top 10 using formulas

Create top 10 using formulas

Creating a Top 10 is quite common when reporting information, especially on dashboards.  It is easiest when working with sorted data, we just select the Top 10 items from the list.  It is also relatively easy when using Auto Filter, Tables and Pivot Tables, as it is a default filter setting within these Excel features.

When creating a Top 10 using formulas on a non-sorted dataset things becomes a little bit trickier.  This post will show you how to create a Top list of any length which even works with duplicate values.

 

Using the LARGE function

It is easy to obtain the Top 10 values, it is getting the Names/Labels which relate to those 10 which is not straightforward.

Look at the example below.  There are 20 customers, with their Sales Values.

Top 10 with Formulas LARGE Function

If we wanted to create a Top 10 of these customers, without sorting the customer list, we could use the LARGE function.  Cell F2 contains this formula:

=LARGE($B$2:$B$21,D2)

LARGE only has two arguments:

=LARGE(Range,k)
  • Range – The range of data to be analyzed.
  • k – The nth item to be found.

In our example the k value is linked to D2, then copied down, so that it will find the Top 10 values (look at the screenshot above).

 

Finding the Labels for the Top 10


Advertisement:

We have the values, so now we can use INDEX/MATCH to find the names of the customers.  Cell E2 in our example would contain the following formula.

=INDEX($A$2:$A$21,MATCH(F2,$B$2:$B$21,0))

Copy this Formula down to Cells E3-E11.  Our worksheet would display as follows:

Top 10 with Formulas Duplicate Names

There is one really big problem – our Top 10 values are not unique.  There are 3 values of 80.  As INDEX/MATCH only returns the first value it finds the name of Customer #1 is returned 3 times.  Yet, Customer #5 and Customer #20 do not even feature on the list.  This is clearly an error.  So, how do we get around this little problem?

 

Finding the Labels when the Top 10 values are not unique

To solve this issue, we will first round the values, then use the ROW function to create a unique extension that will ensure the values are always unique.  Keep reading it will all become clear.

Firstly, we need to decide on an acceptable level of accuracy.  For example, if our numbers are accurate to 2 decimal places we should round the LARGE function to that level.

{=LARGE(ROUND($B$2:$B$21,2),D2)}

This is now an Array formula, do not type the { }, but press Ctrl+Shift+Enter to enter the formula, excel will enter the { } automatically.

Now that we have rounded data we can add a small number to the end of our values to make them unique.  We use the ROW() function for this.  The Formula in Cell F2 will be changed to:

{=LARGE((ROUND($B$2:$B$21,2)+ROW($B$2:$B$21)/10000),D2)}

This formula is also an Array formula, so remember to enter the Formula with Ctrl+Shift+Enter.

By adding the Row value divided by 10000 we are creating unique numbers.  Customer #7 has a value of 120, but once we’ve added the ROW value it is now 120.0008.  Because we rounded the value before adding the ROW number each number is guaranteed to be unique.


Advertisement:

Note, if you rounded the value to 4 decimal places the ROW function will need to be divided by a larger number.  The aim is for the ROW number to add additional decimal places rather than change the value of the rounded number.

Copy the formula in Cell F2 down into F3-F11.

We now apply the same ROUND and ROW functions to the INDEX/MATCH function in Cell E2.

{=INDEX($A$2:$A$21,MATCH(F2,(ROUND($B$2:$B$21,2)+ROW($B$2:$B$21)/10000),0))}

Again, this is an Array formula – enter with Ctrl+Shift+Enter.  Copy this formula down into Cells E3-E11.

Our Top 10 list looks like this:

Top 10 with Formulas No Duplicate Names

Now we can just reduce the number of decimal places displayed to show the correct level of accuracy.

 

Correcting the numbering

There is just one final stage (which is optional).  Our Top 10 includes positions from 1 to 10, but we know that positions 7 to 9 all have the same value, so surely, they should all be given a 7.  That’s how it works in the Olympics, right?  If two competitors have the same result they share the position.

Let’s change our LARGE formula again.  We do not need to use the values in D2-D11 in our formula, we can use the ROW function to create the same value.  Cell F2 will now become:

{=LARGE((ROUND($B$2:$B$21,2)+ROW($B$2:$B$21)/10000),ROW(D2)-ROW($D$2)+1)}

The red highlight section will create the number count from 1-10 when the formula is copied down to Cells F3-F11.


Advertisement:

Now, we must correct the position numbers.  Cell D2 will always be equal to 1, so we can hardcode a value of 1 in that cell.  In Cell D3 we can enter the following formula and copy it down to Cell D11.

=IF(ROUND(F3,2)=ROUND(F2,2),D2,COUNT($D$2:D2)+1)

This formula identifies any rounded values which are the same, and gives them the same position in the Top 10.

The final result now looks like this (notice how the values of 80 all have unique customer names and are all 7th in the Top 10 list.

Top 10 with Formulas Final

 

Will this definitely calculate the correct result?

The only issue with this method is the lost accuracy from rounding the values.  For example, where there are two similar values as shown below

B2 = 60.05634
B3 = 60.05621

These values, if rounded to 2 decimal places, would both round to 60.06.  By applying the ROW function to get unique values, the values would change to:

B2 = 60.062
B3 = 60.063

As a result, these values would switch order in the Top 10.  B3 would be above B2, even though B2 is a larger value.  This is why the rounding accuracy is important.  Provided you round the values to the right level it will give you the correct result.

 

What about a Bottom 10? (or Bottom 11? or Bottom 8?)

Good question, I’m glad you asked.  In the example above just use the SMALL function rather than the LARGE function.  Everything else will be the same.