How to speed up RANK() in Excel

By | February 12, 2018
Questions:

Building a tool in Excel and as part of the exercise I need to identify the top 50 securities based on market cap.

This is an easy enough thing to do using RANK(), but I’ve got a universe of over 10,000 and processing of an otherwise lightening quick workbook gets sluggish once I add the rank over these 10,000 entries.

I realize the sluggishness is due at least in part to Excel needing to sort the entries in order to rank them so I’m wondering if some kind of index on the values to be ranked could speed things up? Is there a better way to do this, either via an Excel function(s) that I’m unfamiliar with or using a some other approach to speed things up?

Answers:

You could sort the values the way you want and then use a function like ROW() to simply assign the rank sequentially (although you might need to pay do something special to break “ties”).

If you don’t want to sort the values, it depends on what you need…

Case 1 — You don’t actually need the rank, but only need to identify the top 50 values.

Suppose your values are in cells A1:A10000.

In another cell, say E1, enter a formula that calculates the “cutoff” for the top 50 values like this =PERCENTILE($A$1:$A$10000,50/COUNT($A$1:$A$10000)).

In cell B1, enter a formula that returns the value in A1 if it is below the “cutoff”, or an empty string otherwise like this =IF(A1<=$E$1,A1,"").

Copy this formula down to cells B2 through B10000.

The rows with top 50 values in column A will have non-blank values in column B.

There may be more than 50 such rows if there are ties.

Case 2 — You need the rank, but only for the top 50 values.

Follow the instructions for Case 1, then, in cell C1, enter a formula that calculates the rank for the non-blank values in column B like this =IF(B1="","",RANK(B1,$B$1:$B$10000)).

Copy this formula down to cells C2 through C10000.

The rows with top 50 values in column A will be ranked in column C.

Again, there may be more than 50 such rows if there are ties. There will also be gaps in the rank values if there are ties.

Case 3 — You need the rank for all 10000 values.

Use RANK like you are now.

Questions:
Answers:

The fastest way to generate a list of the Top 50 is to utilize the auto-filter in Excel, which is faster than any loop you can write.

Here’s an example of how to do it assuming:

  • Row 1 is a header column
  • In the example, column G is the row with the Market Cap

How it works: This is a ducttape solution, but works really well.

  1. Copies the current sheet to a new sheet called “Top 50”
  2. Applies auto-filter and filters it in descending order (highest numbers first)
  3. Deletes all rows after the 50th entry
  4. Select A1 just for to make it pleasing to the eyes 🙂

There are a ton of ways to improve this code such as:

  • Sloopy assumptions on where end of column is
  • Add a column for rank and plug in 1 through 50

I think this code is a good place to start, though. Adjust the column G to the column with your data and give it a try!

Sub FilterbyDescending()
' filter row G in desceneding order
Dim filter As Range
Set filter = Range("A1:G100000")
    filter.Sort Key1:=Range("G2:G1000000"), _
    Order1:=xlDescending, _
    Header:=xlYes, _
    Ordercustom:=1, _
    MatchCase:=False, _
    Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
End Sub

Sub CreateBackup()

Application.ScreenUpdating = False
'Create the Top 50 sheet, paste current sheet to it
Dim top50sheet As Variant
Application.ActiveSheet.Copy After:=Sheets(Application.Worksheets.Count)
Set top50sheet = Application.ActiveSheet
top50sheet.Name = "Top 50"

Dim rowCount As Long
Dim columCount As Long

Call FilterbyDescending
' Time to delete everything after the top 50
Range("A52").CurrentRegion.Select
rowCount = Selection.Rows.Count
columnCount = Selection.Columns.Count
Range(Cells(52, 1), Cells((rowCount), columnCount)).Delete

Cells(1, 1).Select
Application.ScreenUpdating = True

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *