Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    30

    Unanswered: Calculating a Percentile

    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. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183

    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.
    Attached Thumbnails Attached Thumbnails percentage.jpg  
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    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
  •