How to find the Percentile Rank from an array given a value ?? [easy for most]

Hey guys,

I have an array of values [stock prices], in column D there is a value that represents the current value and I am trying to translate that to a percentile given the array of values over the last 1yr.

The Problem:

If the current value doesn't line up tot he exact decimal point to any value in the array than the formula returns an error. If however I have an exact value from the table it works. Here's the problem, because I am dealing with 2 decimal places - it almost will never line up to one of the combinations in the array.

The GOAL:

How can I make this formula work that no matter what the current value is in Column D, it will tell me the percentileranking in the array - regardless if it isn't an exact match.


Pls anything would help!

The code is working as intended. You will receive the error (#N/A) when the number in Last (Column D) is lower than the MIN number or higher than the MAX number.

Originally Posted by Syrkrasi

The code is working as intended. You will receive the error (#N/A) when the number in Last (Column D) is lower than the MIN number or higher than the MAX number.

Okay I see you are right. I have a follow up question. Would there be a way to have a formula to identify if the number in Last (Column D) is lower than the MIN number and put 0 or MAX number and put 100....

Would be nice if I can work around this so when these cases happen it knows its the smallest value --> 0th percentile or max value --> 100th percentile