PercentRank Array Formula returns Error

[HalPlz]

I'm assuming I just don't understand what the formula I'm trying to use is actually doing. I'm trying to use an If function to narrow down my array field to use multiple criteria.

I am trying to find the percent rank of each value in column B, but only count values in the array that satisfy criteria in columns D, E, and F, where D = whatever the value in the row D is, and row E = "No" and row F = 1.

The formula I'm using, with ctrl + shift + enter:
=PERCENTRANK.EXC(IF(($F$1:$F$18=1)*($D$1:$D$18=$D1)*($E$1:$E$18="No"),$B$1:$B$18),B1)

The formula seems to work (or at least doesn't return an error) for all cells except the top 3. If I remove the ($F$1:$F$18=1) from the formula, then no errors are returned. If I change one of the values in column F to 1, then it doesn't return an error. It just doesn't make sense to me because there are other rows that don't return an error that have the same number of correct parameters in the row. There's nothing (seemingly) different about the rows that return an error.

Any help would be greatly appreciated!

[Bo_Ry]

At G1
=IF(($F$1:$F$18=1)*($D$1:$D$18=$D1)*($E$1:$E$18="No"),$B$1:$B$18)
={FALSE;FALSE;FALSE;2.89991;FALSE;FALSE;3.20161;3.64416;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
return 3 number
2.89991, 3.20161 ,3.64416

B1 =0.4433 is not in the range of 2.89991-3.64416 #N/A for out of the range.

Other rows return value because in is in the range.

[HalPlz]

Ah, I get it. My formula is not wrong at all - only my understanding of the percentile rank. I guess I can just make an IFERROR and call it a day.

[jason.b75]

I'm assuming I just don't understand what the formula I'm trying to use is actually doing.

The formula seems to work (or at least doesn't return an error) for all cells except the top 3.

I guess I can just make an IFERROR and call it a day.

The real question here, is do you know the results that you want?

I've compressed your sample file to 3 rows, are the results of the formula what you would expect?

In my opinion, the first one looks wrong, but that could simply be because I don't know what is expected.

Please Login or Register  to view this content.