ExcelBanter - Rank excluding Errors

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Rank excluding Errors (https://www.excelbanter.com/excel-worksheet-functions/137530-rank-excluding-errors.html)

Rank excluding Errors

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

Rank excluding Errors

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)

On 2 Apr, 12:52, "Werner Rohrmoser"
wrote:
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

Rank excluding Errors

Hi Lori,

simply great!
Thanks!

Werner

Rank excluding Errors

Thanks for the reply, happy to be of assistance.

On 2 Apr, 14:02, "Werner Rohrmoser"
wrote:
Hi Lori,

simply great!
Thanks!

Werner