PERCENTRANK in array formula: strange behavior

[vezerid]

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

 

[Domenic]

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!

 

[vezerid]

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

 

[Domenic]

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!

 

[Kimmerz321]

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