Rank excluding Errors

[Werner Rohrmoser]

Hello,

I've searched for a solution here, but I'm not able to find any.

My formulas:
=RANK(C12;IF(ISNUMBER(C$7:C$55);C$7:C$55;"");0)
or
=RANK(C13;IF(ISERROR(C$7:C$55);"";C$7:C$55);0)
entered as an array formula.

The range C7:C55 contains error values like #N/A.
I tried to exclude them like the way I' ve done it before with AVERAGE
or MAX function,
but it doesn't work.

I would appreciate to get a solution for this problem.
Thanks.

Regards
Werner

 

[Lori]

The middle argument for Rank needs to be a reference not an array:
=rank(number,ref,order). Try this alternative which ignores errors:

=COUNTIF(C$7:C$55,">="&C12)

 

[Werner Rohrmoser]

Hi Lori,

simply great!
Thanks!

Werner

 

[Lori]

Thanks for the reply, happy to be of assistance.