Ask Your Question

=Rank #value! (remove) [closed]

asked 2016-07-21 04:57:25 +0100

JG101 gravatar image

original - but returns #value! =RANK(I16,$I$16:$I$313,0)

I would like it to return a blank instead of #value! how do I fix this function.


thank you in advance for any help.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by JG101
close date 2016-07-22 14:47:44.453350

1 Answer

Sort by » oldest newest most voted

answered 2016-07-21 05:02:22 +0100

mark_t gravatar image

IFNA will only trap #N/A errors, use IFERROR to trap all error types.

edit flag offensive delete link more


ty Mark for the info


this does not work, how to correct

JG101 gravatar imageJG101 ( 2016-07-21 05:16:04 +0100 )edit

I've tried these:

=RANK(I16,IF(ISERROR(I$16:I$313),"",I$16:I$313),0) =RANK(I16,IF(ISNUMBER(I$16:I$313),I$16:I$313,""),0) =IF(iferror(RANK(i16,i$16:i$313,1)),"",RANK(i16,i$16:i$313,0))

still shows #value!

JG101 gravatar imageJG101 ( 2016-07-21 05:27:12 +0100 )edit

i try this:


does not work

JG101 gravatar imageJG101 ( 2016-07-21 05:31:32 +0100 )edit

How do you mean not working?

Does it still show #value, or not giving the correct result?

Make sure I16 is numeric and not text, check there is no space or '.

mark_t gravatar imagemark_t ( 2016-07-21 05:32:12 +0100 )edit

found what worked


JG101 gravatar imageJG101 ( 2016-07-21 05:33:40 +0100 )edit

try =IFERROR(RANK(i16,$i$16:$i$313)),"")

mark_t gravatar imagemark_t ( 2016-07-21 05:34:24 +0100 )edit

Question Tools

1 follower


Asked: 2016-07-21 04:57:25 +0100

Seen: 44 times

Last updated: Jul 21 '16