Ranking a column with some cells having #value!
 

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.

Ranking a column with some cells having #value!
 

Why dont 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!

--------------------
(Ms-Exl-Learner)
--------------------


"JB" wrote:

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.

Ranking a column with some cells having #value!
 

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

--
Biff
Microsoft Excel MVP


"JB" wrote in message
...
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.