what is the largest gap between rank and approximate rank?

Reordering rank values

• Dear I have a spreadsheet which has a list of business names (each unique) [column A] and a corresponding numeric value [column B], with a rank calculation in column C. Sometimes there are multiple scores of the same value. This results in a number of businesses generating the same rank value (anywhere from 2 to 50 records with the same rank value). Unfortunately, because the purpose of the work is to report on performance impartially, it is not acceptable to use a ranking formula which will give each a unique rank. At present the data is sorted manually using filters to provide the base data to produce rank tables and charts. I want to be able to use formula (I can¡¯t use macros) to take the ranks calculated in column C and list them in descending order automatically. I then want the business name to be generated next to the rank. I have tried the following formulas which worked in an example where I only had a maximum of two rank values the same, but of course they don¡¯t hold up when a larger number of ranks are the same. For the rank - =INDEX($C$3:$C$203,MATCH(SMALL(COUNTIF($C$3:$C$203,"<"&$C$3:$C$203),ROW($C1:$C1)),COUNTIF($C$3:$C$203,"<"&$C$3:$C$203),0)) For the business name ¨C (column D holds the above formula) =IF(D3=D2,INDEX($A$3:$A$203,SMALL(IF(D3=$C$3:$C$203,ROW($C$3:$C$203)),2)-MIN(ROW($C$3:$C$203))+1),INDEX($A$3:$A$203,MATCH(D3,$C$3:$C$203,0))) I have been learning a lot of new formula and reading posts such as yours to help me get to this point, but as my knowledge is limited and many of the examples seem to order text rather than numbers ¨C I am stuck! I would really appreciate any help you can offer Many Elizabeth

• Answer:

  Elizabeth, If appears your formula for column D works fine for multiple rank values being the same. The problem appears to be in getting the matching name from column A. Replacing you second formula with this formula (placed in E3) =INDEX($A$1:$A$203,SMALL(IF($C$3:$C$203=$D3,ROW($C$3:$C$203)),COUNTIF($D$3:$D3,$D3)),1) entered with Ctrl+Shift+Enter because it is an array formula (like yours). Then drag fill it down the column. Note that this is not a typo: COUNTIF($D$3:$D3,$D3) The $D$3 fixes the base in D3 and since it is entered in row 3, it is terminated in row 3 with $D3. However, as you drag fill it down the column, the $D3 becomes $D4 and thus it counts how many of the values in C3 have occured up to that point. I use this as the second argument to the small function. The first argument will just contain an array with the row numbers of the rows that match the rank in the current row in column D in column C (and the value false for those that don't). This allows you to select the sequential Business Names in the same order they appear in column A. I believe that is what you want. (in my tests, it worked fine - so hopefully I understand the situation).