Solved

Calculating a percentile

Posted on 2011-02-24
1
260 Views
Last Modified: 2012-05-11
In column A are dates (in order, from 1986 to the present).  In column B are a range of values, so each day has a corresponding value.  Using the first x days of column B values as my sample (for example, 50 days), I need to calculate a value such that a given percentage of the sample values (say, 15%) are greater than that value and the rest are below that value.
0
Comment
Question by:pwflexner
1 Comment
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 250 total points
ID: 34972673
Assuming that your first data row is 2 and the percentile is in cell C1 and the population size is in D1:

   =SMALL(OFFSET(B2,0,0,D1,1),INT(C1*D1)+1)

Kevin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now