I have a list of ranks but if there are duplicate numbers it skips the next rank.
For example I get:
#, Rank
1,1
2,2
2,2
3,4
4,5
4,5
5,7
I want:
#, Rank
1,1
2,2
2,2
3,3
4,4
4,4
5,5
This is what I found to work on Excel but can't use it in Numbers:
=SUMPRODUCT((A$1:A$22>A1)/COUNTIF(A$1:A$22,A$1:A$22&""))+1
Here is a two column solution. As far as I can tell it works and does not require the list to be sorted. The problem with the Excel solution is it is an array function. Numbers does not support array functions so it usually takes a few columns to do the same work.
A =the list of numbers starting at A2
B=IF(COUNTIF(A2:A$22,A)>1,A,"")
C=COUNTIF(A,"<"&A)+1-COUNTIF(B,"<"&A) = the rankings
You can do this by incrementing the count whenever the rank changes, as in the following.To use this particular formula you need to 'seed' the counter by manually entering 1 in the first adjusted rank cell.
The following table starts in B1 (# is column B, Rank is column C and Adjusted rank is column D).
# | Rank | Adj Rank | |
1 | 1 | 1 | << Put the value of 1 here |
2 | 2 | 2 | << Use this formula here and fill down: =IF(C3=C2,D2,1+D2) |
2 | 2 | 2 | |
3 | 4 | 3 | |
4 | 5 | 4 | |
4 | 5 | 4 | |
5 | 7 | 5 |
That would work except for the fact that the Ranks would be out of order. I need something more for no matter what order the ranks are in, it can calculate properly.
That's it...Thanks so much for your help..
Rank without skipping numbers
