RANK - how to exclude errors?

[servcoor]

Any ideas? I have a column of data several rows deep. Some cells must display #DIV/0! error. I need to rank the values in the column but RANK does not work when errors are included in the range. Can I enter a formula with RANK to exclude errors? Here are seven rows of my data, N9:N15. Any suggestions are greatly appreciated!


35.00
#DIV/0!
67.20
49.04
47.98
#DIV/0!
42.81

 

[ChrisUK]

Set your range to the appropriate size and use this code. I know it delete all errors and not just #DIV/0! but I thought this is what you'd want.

Set rng = Range("I1:I20")
For Each c In rng
If IsError(c.Value) Then c.Delete
Next

 

[Aladin Akyurek]

Any ideas? I have a column of data several rows deep. Some cells must display #DIV/0! error. I need to rank the values in the column but RANK does not work when errors are included in the range. Can I enter a formula with RANK to exclude errors? Here are seven rows of my data, N9:N15. Any suggestions are greatly appreciated!


35.00
#DIV/0!
67.20
49.04
47.98
#DIV/0!
42.81

Book12
	M	N	O	P	Q
9		35	5
10		#DIV/0!
11		67.2	1
12		49.04	2
13		47.98	3
14		#DIV/0!
15		42.81	4
16
Sheet1

O9, copied down:

=IF(ISNUMBER(N9),1+COUNTIF($N$9:$N$15,">"&N9),"")