How to obtain a value above certain percentage?

[trackfiend]

I have a range of values in a column and would like to get the first value above the minimum 10% of the values given.

Basically, I want to ignore the bottom 10% of values and find the next higher up value.

Any suggestions?

[shg]

Maybe =MIN(IF(PERCENTRANK(A1:A20, A1:A20) >0.1, A1:A20))

This is an array formula, meaning it MUST be confirmed with Ctrl+Shift+Enter, not just Enter. (With the cursor in the formula bar, hold down Ctrl and Shift, then press Enter.) You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.

[trackfiend]

Initially I used:

=MIN(IF($E$2:$E$3125>(0.1*MAX($E$2:$E$3125)-MIN($E$2:$E$3125)),$E$2:$E$3125))

I also used the array tool {}.

The above gave a smaller value from the range and yours seems to give a slightly higher one. Thanks for the response.

[shg]

Percentrank returns percentiles (e.g., the lowest 100 of 1000 scores are the lowest tenth percentile, irrespective of the score values).

Use whichever gives what you want.