Explanation
This formula breaks ties with a simple approach: this first tie in a list “wins” and is assigned the higher rank. The first part of the formula uses the RANK function normally:
=RANK(C5,points)
Rank returns a computed rank, which will include ties when the values being ranked include duplicates. Note that 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:
COUNTIF($C$5:C5,C5)-1
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.
Explanation
Excel contains a number of rounding functions. Two of these functions, the INT function and the TRUNC function will return the integer portion of a number that contains a decimal value.
The INT function behaves a bit differently with negative values, so in this example we are using the TRUNC function. The TRUNC function simply truncates (i.e. removes) decimal values if they exist – it doesn’t do any rounding.
In the example shown, cell C6 contains this formula:
=B6-TRUNC(B6)
The TRUNC function returns the integer portion of the number which is then subtracted from the original value. The result is the decimal portion of the number.
If the original number is an integer to begin with, the result of this formula is zero.
