where “points” is the named range Rank returns a computed rank, which will include ties when the values being ranked include duplicates. Note the the RANK function by itself will assign the same rank to duplicate values, and skip the next rank value. You can see this in the Rank 1 column, rows 8 and 9 in the worksheet. The second part of the formula breaks the tie with COUNTIF: Note the range we give COUNTIF is an expanding reference: the first reference is absolute and the second is relative. As long as a value appears just once, this expression cancels itself out – COUNTIF returns 1, from which 1 is subtracted. However, when a duplicate number is encountered, COUNTIF returns 2, the expression returns 1, and the rank value is increased by 1. Essentially, this “replaces” the rank value that was skipped originally. The same process repeats as the formula is copied down the column. If another duplicate is encountered, the rank value is increased by 2, and so on.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
