Solved

Calculating a percentile

Posted on 2011-02-24
1
262 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

861 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