I am wondering if it would be possible to find where the 85th percentile falls using a FUNCTION like match. In this screenshot the 85th percentile would be at 61. Using MATCH I could find what position the 85th is in to then use if statements to print out where it corresponds to, in this case 61. If there is an easier way to do this without using MATCH please let me know!

edit: the 85th should be found using observations on the far right column. for this example there is 100 observations so the 85th would be 15 observations from the top.

CodePudding user response：

I think this should come with a bit of a health warning - there are 9 ways of calculating a percentile listed

Really this answer is just a starting point because (a) there is a lot more that could be said about the way that the percentile should be estimated and what to do when it works out to be between two points, and (b) the formulas could be more sophisticated - in Excel 365 it could be done fairly easily with a single formula.

Edit 1

I have added the formulas for percentile.exc (N 1)P and percentile.inc (N-1)P 1 in G3 and G4 for comparison. In this case all three formulas give the same end result (percentiles are between 14 and 17 so will map to 61). Just to clarify, I'm not actually using percentile.exc or percentile.inc because they would require the raw data. I only need N and P to get an estimate of the percentile expressed as a position within the data (e.g. 15.15 means that it lies somewhere between the 15th and 16th data points counting from the top of the diagram).

Percentile.exc

=(1-F$2/100)*(SUM(C$2:C$30) 1)

Percentile.inc

=(1-F$2/100)*(SUM(C$2:C$30)-1) 1