PERCENTRANK in array formula: strange behavior

Hi all,

I was trying to help an OP with a problem involving PERCENTRANK. We
wanted to compute the average of those numbers in a dataset with
percentile between 60% and 95%. The suggested formula was:

=AVERAGE(IF(AND(PERCENTRANK(A1:A10,A1:A10)>0.6,PERCENTRANK(A1:A10,A1:A10)<0.95),A1:A10))

This one returns #N/A if A1:A10 is empty and 0 if it is non-empty. The
docs for PERCENTRANK do not mention #N/A as possible return value and
neither do for AVERAGE. Yet it might make sense for an empty data set.
The 0 though I cannot explain. I tried various versions involving N(),
just in case, like:

=AVERAGE(IF(AND(PERCENTRANK(N(A1:A10),N(A1:A10))>0.6,PERCENTRANK(N(A1:A10),N(A1:A10))<0.95),N(A1:A10)))

Still no luck. If I break it down to auxiliary columns and w/o array
formulas it works:

In column B:B:
=PERCENTRANK($A$1:$A$10,A1)
In column C:C
=IF(AND(PERCENTRANK($A$1:$A$10,A1)>0.6,PERCENTRANK($A$1:$A$10,A1)<0.95),A1)

In a separate cell:
=AVERAGE(C1:C10)

Any explanation? I am baffled. Far more complex formulas have worked in
the past.

Regards,
Kostis Vezerides

The AND function doesn't work with arrays. It returns a single value.
Try the following instead...

=AVERAGE(IF(PERCENTRANK(A1:A10,A1:A10)>0.6,IF(PERCENTRANK(A1:A10,A1:A10)<
0.95,A1:A10)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <[email protected]>,
"vezerid" <[email protected]> wrote:

> Hi all,
>
> I was trying to help an OP with a problem involving PERCENTRANK. We
> wanted to compute the average of those numbers in a dataset with
> percentile between 60% and 95%. The suggested formula was:
>
> =AVERAGE(IF(AND(PERCENTRANK(A1:A10,A1:A10)>0.6,PERCENTRANK(A1:A10,A1:A10)<0.95
> ),A1:A10))
>
> This one returns #N/A if A1:A10 is empty and 0 if it is non-empty. The
> docs for PERCENTRANK do not mention #N/A as possible return value and
> neither do for AVERAGE. Yet it might make sense for an empty data set.
> The 0 though I cannot explain. I tried various versions involving N(),
> just in case, like:
>
> =AVERAGE(IF(AND(PERCENTRANK(N(A1:A10),N(A1:A10))>0.6,PERCENTRANK(N(A1:A10),N(A
> 1:A10))<0.95),N(A1:A10)))
>
> Still no luck. If I break it down to auxiliary columns and w/o array
> formulas it works:
>
> In column B:B:
> =PERCENTRANK($A$1:$A$10,A1)
> In column C:C
> =IF(AND(PERCENTRANK($A$1:$A$10,A1)>0.6,PERCENTRANK($A$1:$A$10,A1)<0.95),A1)
>
> In a separate cell:
> =AVERAGE(C1:C10)
>
> Any explanation? I am baffled. Far more complex formulas have worked in
> the past.
>
> Regards,
> Kostis Vezerides

It helps a lot!

Thank you Domenic, I can't believe this detail had slipped my
attention. In the past I had found workarounds but had not clarified it
in myself that I cannot use it in array formulas. And of course I
neglected using the infix notation, which in this case replaces AND
perfectly:

=AVERAGE(IF((PERCENTRANK(A1:A10,A1:A10)>0.6)*(PERCENTRANK(A1:A10,A1:A10)<0.95),A1:A10))

Regards

Kostis

PS. I always look upon your posts with great interest. Your formulas
taught me a lot in aspects of Excel I had not had the chance to work
with prior to joining these groups systematically

In article <[email protected]>,
"vezerid" <[email protected]> wrote:

> Thank you Domenic,

You're very welcome, Kostis!

> I can't believe this detail had slipped my
> attention.

When I first started, I ran into the same thing...

> PS. I always look upon your posts with great interest. Your formulas
> taught me a lot in aspects of Excel I had not had the chance to work
> with prior to joining these groups systematically

Yes, these newsgroups are great. I continue to learning something new
everyday...

Cheers!

Dredging this up.......

I need to take the average of a range of numbers if they're in a certain percentile, I.E in the 90th to 99th Percentile. I tried using the formula below, but Excel errors out on the PercentRank function - it seems that it doesn't like that I'm giving it two arrays instead of an array and a value as directed by the specs. I get a #VALUE error. Here is the formula I am using:


=AVERAGE(IF(PERCENTRANK(B1031:B1530,B1031:B1530)>0.9,IF(PERCENTRANK(B1031:B1530,B1031:B1530)<0.99,B1031:B1530)))

Suggestions? Am I using too large of a range?

Thanks, Kim