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,PER CENTRANK(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