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

    Unanswered: Calculating a Percentile

    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
    Kerala, India

    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 (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
    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