Ranking of cells from 1 to 20 with ties

[Guest]

I'm working on a spreadsheet for sports and want to rank certain stats from 1
to 20, to include a 'T' if there is a tie betwen scores. Example: 1, T 2, T
2, 4 ...

I tried using the =if function and got the proper results for the first 8
scores, but because of the limitation of 8 =if's in a cell I cannot have it
check the rest of the field. Is there a way to combine cells to continue with
this function? (have another cell pick up where the first one left off)

Sorting the stats in order from 1st to 20th will not work because it
interferes with other sheets where these stats get used.

N3:N22 is where that stats are located, and L3:L22 is where I want the
rankings displayed. If needed, another column could be inserted before the L
column to display the 'T' if the stats are tied.

I would prefer (if possible) of not using a macro for this.

 

[Guest]

I'm working on a spreadsheet for sports and want to rank certain stats from 1
to 20, to include a 'T' if there is a tie betwen scores. Example: 1, T 2, T
2, 4 ...

I tried using the =if function and got the proper results for the first 8
scores, but because of the limitation of 8 =if's in a cell I cannot have it
check the rest of the field. Is there a way to combine cells to continue with
this function? (have another cell pick up where the first one left off)

Sorting the stats in order from 1st to 20th will not work because it
interferes with other sheets where these stats get used.

N3:N22 is where that stats are located, and L3:L22 is where I want the
rankings displayed. If needed, another column could be inserted before the L
column to display the 'T' if the stats are tied.

I would prefer (if possible) of not using a macro for this.

Add on:

I am having some luck with the =large function, in displaying the proper
ranking without the 'T' (I think I can add that on later with another
function to display in another column), but I reached a limit of 7 =if's and
6 =large's in the cell.

 

[Aladin Akyurek]

Have a look at the RANK function.

 

[Guest]

I also prefer worksheet/workbook methods to macros. They are easier to debug,
and a year from now I am more likely to know what is going on in a worksheet
than in a macro.
I have something that seems to be what you are looking for. Mine is for only
9 scores where you need 20, and the scores are not where you want them. You
should be able to do the necessary modifications.
Cells A2 to A10: the names
Cells B2 to B10: the scores
Cells E2 to E10: numbers 1 to 9
Cell G2: =A2
Cell H2: =B2
Cell J2: =B2+ROW(I2)/1000
Cell K2: =LARGE($J$2:$J$10,E2)
Cell L2: =SUMPRODUCT($E$2:$E$10,--($J$2:$J$10=K2))
Cell M2: =VLOOKUP(L2,$E$2:$H$10,4)
Cell N2: =VLOOKUP(L2,$E$2:$H$10,4)
Cell O2: =VLOOKUP(L2,$E$2:$G$10,3)
Cells G2 to O2 are copied down

 

[Ron Rosenfeld]

I'm working on a spreadsheet for sports and want to rank certain stats from 1
to 20, to include a 'T' if there is a tie betwen scores. Example: 1, T 2, T
2, 4 ...

I tried using the =if function and got the proper results for the first 8
scores, but because of the limitation of 8 =if's in a cell I cannot have it
check the rest of the field. Is there a way to combine cells to continue with
this function? (have another cell pick up where the first one left off)

Sorting the stats in order from 1st to 20th will not work because it
interferes with other sheets where these stats get used.

N3:N22 is where that stats are located, and L3:L22 is where I want the
rankings displayed. If needed, another column could be inserted before the L
column to display the 'T' if the stats are tied.

I would prefer (if possible) of not using a macro for this.

Add on:

I am having some luck with the =large function, in displaying the proper
ranking without the 'T' (I think I can add that on later with another
function to display in another column), but I reached a limit of 7 =if's and
6 =large's in the cell.

To Rank the statistics, in L3 enter the formula:

=RANK(N3,$N$3:$N$22)

and copy/drag down to L22.

In the "inserted" column, enter the formula:

=IF(COUNTIF($L$3:$L$22,L3)>1,"T","")

and copy/drag down to row 22.


--ron