Ranking a column with some cells having #value!

[JB]

I have a spreadsheet that has 20 rows. Depending on the input, not all rows
will be entered with data. Those without data will have #value!.

Problem is how can I formulate the ranking so that I can always see the
ranking of the 20 rows and the ranking will auto exclude those rows having
#value!?

Thank you.

 

[Ms-Exl-Learner]

Why don’t you post your formula which is resulting #value!. Otherwise try
this.

=IF(ISERROR(Your Formula),0,Your Formula)

Which will get you the result of 0 instead of #value!.

Remember to Click Yes, if this post helps!

 

[T. Valko]

Try this...

A1 = 59
A2 = 93
A3 = 28
A4 = #VALUE!
A5 = 44

Array entered** in B1 and copied down to B5:

=IF(ISERROR(A1),"",SUM(IF(ISNUMBER(A$1:A$5),--(A1<A$1:A$5)))+1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Returns these ranks:

59 = 2
93 = 1
28 = 4
#V = blank
44 = 3