rank function

[donl]

Is there a way to use the rank function when the range you are selecting from has occasionally a cell = #N/A. For example:

(1,2,5,4,8,#N/A,12,14)

I want to rank these in a separate column but I get #N/A for every number because there is one N/A# in the range area.

Thanks.

[Domenic]

Assuming that Column A contains your numbers, try...

B1, copied down:

=SUM(IF(1-ISNA($A$1:$A$10),--(A1<$A$1:$A$10)))+1

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

[olasa]

This is one option:
=COUNTIF(A2:A20,">"&A2)+1.....+COUNTIF(A2:A20,#N/A).....
In the example "1" can have rank 6 or 7, or 1 or 2 depending on if #N/A is included in the ranking.

Ola Sandström

[donl]

i tried these two but here is what I get:
1----->1
#N/A----->1
2----->1
3----->1
4----->1
5----->1
6----->1
7----->1
8----->1
9----->1

when I place the function: =SUM(IF(1-ISNA($A$1:$A$10),--(A2<$A$1:$A$10)))+1 it returns a ranking of 1 for each cell.

the other function posted gives me an error. any suggestions? thanks again.

[donl]

=SUM(IF(1-ISNA($A$1:$A$10),--(A3<$A$1:$A$10)))+1

ok, my bad, this function works when I press shift+ctrl+enter. however, how can I make this work without hitting shift+ctrl+enter. and lastly, it is ranking backwards. is there a way to reverse this?

1-->6
#N/A-->#N/A
2-->5
3-->4
4-->3
5-->2
6-->1

thanks once again.