Yvonne Love
Member
I used to rank clients using the rank formula, and then I switched to showing values as rank (highest to lowest), which I think works better for my needs. However, I was testing to make sure both were producing the same result, and it turns out they are different. Using the formula, if there are two identical values, it will count both as the same rank and skip the next number for the next highest value (e.g., rank 3, 3, then 5); in the pivot table, it also counts both identical values as the same rank, but it does not skip the next number (e.g., rank 3, 3, then 4). I think the formula way is the better option, so I wanted to find out if there may be a setting or something that will allow the pivot table ranks to operate in the same way as the formula.
I've attached a test spreadsheet showing what I mean. The pivot tables show the "show value as rank", and the table below it is calculating rank by the formula. The first three columns were the source of the data (random - RANDBETWEEN), and IJK are the adjusted valued columns which are the source of the pivot table.
I've attached a test spreadsheet showing what I mean. The pivot tables show the "show value as rank", and the table below it is calculating rank by the formula. The first three columns were the source of the data (random - RANDBETWEEN), and IJK are the adjusted valued columns which are the source of the pivot table.
