In Excel, how can I PercentRank only values above a certain threshold in one column?

Question

In Microsoft Excel, I'm looking to percentrank values in one column, but I'd only like to rank those that are that are above 1,000. All the others that are below 1,000, I'd like to leave a zero. Here's example data:

750,000

1,500,000

10

50

500,000

In this scenario, in the column to the right, I'd like to only percent rank the 500k, 750k, and 1.5M numbers, while leaving a 0 for the 10 and 50. How can I do this in one formula?

I've tried a few different ways, such as combining IFs and PercentRank. If possible, I'd like to avoid creating another column to do this.

=IF([cell with 750,000]>"5000",PERCENTRANK.INC([cell range of the values]>"5000",[cell with 750,000),0)

Answer 1

Filter out unwanted values so it returns array of {750000,1500000,500000} with formula:

=FILTER($A$1:$A$5,$A$1:$A$5>1000)

Use it as array input for PERCENTRANK.INC:

=PERCENTRANK.INC(FILTER($A$1:$A$5,$A$1:$A$5>1000),A1)

Combine with IF to show 0's for unwanted values:

=IF(A1<=1000,0,PERCENTRANK.INC(FILTER($A$1:$A$5,$A$1:$A$5>1000),A1))

Result: