Jul 14 2019 08:03 AM
I'm working on an Excel file that contains roughly 622 numbers. I'm trying to use the RANK.EQ function to capture the rank of each number in the list. Duplicate numbers should have the same rank (e.g., if the list goes 229, 216, 200, 198, 198, 197, the rank should be 1, 2, 3, 4, 4, 6).
I'm using the following formula:
=RANK.EQ(D2,D2:D623,1)
D2 contains the first number in the list, D2:D623 is the range. I keep getting the #N/A error and can't figure out why no values are returned. The data in cells D2 to D623 are formatted as numbers.
Does anyone have some experience with this formula that can help me understand what I'm overlooking? Any assistance would be appreciated.
Jul 14 2019 08:27 AM
Jul 14 2019 08:29 AM
you can try following formula
=RANK.EQ(D2,$D$2:$D$623,1)
It is just that you might need to use the absolute reference for D2:D623
Jul 14 2019 08:33 AM
Jul 14 2019 08:35 AM
Jul 14 2019 08:41 AM
SolutionAs variant your numbers could be formatted as text, you may check in any empty cell as =ISTEXT(D2).
Jul 14 2019 08:54 AM
Jul 14 2019 08:41 AM
SolutionAs variant your numbers could be formatted as text, you may check in any empty cell as =ISTEXT(D2).