1. Registered User
Join Date
Mar 2010
Posts
30

Hi,
I'm trying to calculate different percentiles from an array of data that i have, with no luck at all.
In column A i have dates for each day, and in column B i have a value for that specific date. Dates are not necessarily consecutives. For example:

A B
9/24/09 3.5
10/22/09 8.3
11/26/09 18.9

I would like Excel to return the date and value from column B, for the 10%, 25%, 50%, 75% and 90%, of the SUM of values in column B.
In the above example, the total SUM of values is 30.7, so the 10th percentile is 2.07, and the date that it should return is 9/24/09, as this is the first day that the accumulated values in column B went over the 10%.

Any help will be greatly appreciated.
Thanks a lot!

2. Registered User
Join Date
Jan 2009
Location
Kerala, India
Posts
188

Calculating a Percentile

Attempted a sample study of your excel problem with the layout of values as shown in the attached image. The VLOOKUP() Function is used for finding the data.

You may improve this method with more data as well as the usage of VLOOKUP() Function input 10%, 25% etc. using these values into a column as a list and copy pasting vlookup() function.

3. Registered User
Join Date
Mar 2010
Posts
30
Thanks a lot. I'll give it a shot.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•