To rank data that is not tied in the Excel2013

When we are inconvenient or unable to sort the data, we need to rank the data (ranked), many people choose to use the Rank function, but the rank function has a specific: for the same data (duplicate data) ranking is ranked, and will occupy the empty position. For example, ranking 100, 100, and 99 results in 1, 1, 3, instead of 1, 2, 3, 1, 1, 2, respectively. Now, let's teach you how to solve this problem.

Case demonstration

① start Excel2013, small series of the first slightly produced a table, see some side-by-side data, in the F2 cell input formula: =rank (E2, $E $: $E $) +countif ($E $1:e2,e2)-1

② carriage return, get the result 6, we verify that, see 5 is not ranked in the column data 6th place.

After the ③ is verified, the mouse moves to the lower-right corner of the cell, appears with the + number, and double-click the fill handle to finish sorting the remaining data.

④ sorting completed, we can not trust their own eyesight verification, 2 tied 9 is not all ranked 2nd, one 2nd another 3rd, according to the data in order.

Formula description

Rank (to what rank, where): Rank cell values in a contiguous set of cells.

COUNTIF (where to count, what counts): calculates the specified number of items within a specified range.

Meaning of the formula: E2 rank + number of E2 above current row-1