Dynamic Rank Formula: No Duplicates or Errors

[ReturnAg08]

I've seen a number of threads that help with creating rank formulas that solve the duplicates problem by using COUNTIF statements, and others that allow for removal of blanks or #DIV/0! errors, but I'm using a set of data where cells are updating dynamically, and some of them might be numbers at some point, and other times it will show the #N/A error.

Current Formula: RANK(X143,$X$143:$BE$143,1)+COUNTIF($X$143:X143,X143)-1

Columns X through BE have the numbered values, though depending on how the page is updated the columns towards the end might be turned into the #N/A error. I've tried some IFERROR or IF(ISNA()) type things, but either I'm not on the right track or I'm setting it up incorrectly. The values need to be kept as columns, and I can't use VBA here.

Link 1 Source (no blanks): excel - Truly unique ranking (while ignoring empty cells) - Stack Overflow
Link 2 Source (#DIV/0 error): http://www.mrexcel.com/forum/excel-questions/144896-rank-how-exclude-errors.html

 

[Matt Rogers]

Hi.

Suggestion:

	A	B
1	Value	Rank
2	200	2
3	120	3
4	1000	1
5	Text	0
6	50	4
7		0
8	1000	1

<tbody>
</tbody>

Array-Formula (Press CTRL-SHIFT-Enter) B2:
{=ISNUMBER(A2)*SUM(IF(IF(ISNUMBER(A$2:A$100)*(COUNTIF(INDIRECT("A1:A"&ROW($A$2:$A$100)),A$2:A$100)=1),A$2:A$100,MIN(A$2:A$100)-1)>A2,1),1)}

 

[ReturnAg08]

Matt,

If I've modified your formula correctly, then it looks like what I've copied below. That works for ranking the first cell, but it's not working when I copy it across to the other cells:

=ISNUMBER(X143)*SUM(IF(IF(ISNUMBER($X$143:$BE$143)*(COUNTIF(INDIRECT("X143:BE"&COLUMN($X$143:$BE$143)),$X143:BE$143)=1),$Y$143:$BE$143,MIN($Y$143:$BE$143)-1)>Y143,1),1)

 

[ReturnAg08]

Matt,

If I've modified your formula correctly, then it looks like what I've copied below. That works for ranking the first cell, but it's not working when I copy it across to the other cells:

=ISNUMBER(X143)*SUM(IF(IF(ISNUMBER($X$143:$BE$143)*(COUNTIF(INDIRECT("X143:BE"&COLUMN($X$143:$BE$143)),$X143:BE$143)=1),$Y$143:$BE$143,MIN($Y$143:$BE$143)-1)>Y143,1),1)

Meant to also say that, yes, I did enter it as an array so it has the curly brackets.