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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Value[/TD]
[TD="align: center"]Rank[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: center"]Text[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]


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.