+ Reply to Thread
Results 1 to 4 of 4

VBA Function that interpolates a value from known percentiles 25th, 50th, 75th and 90th

  1. #1
    Registered User
    Join Date
    11-02-2016
    Location
    Lexington, Kentucky
    MS-Off Ver
    2013
    Posts
    2

    Smile VBA Function that interpolates a value from known percentiles 25th, 50th, 75th and 90th

    Hi!

    I have a vba function that calculates the percentile of a value when given the 25th, 50th, 75th and 90th percentiles. I would like to manipulate the function so that it performs the opposite, where I input a percentile (ex. 60th percentile) and it calculates the corresponding value using the 25th, 50th, 75th and 90th percentiles. In statistical terms, I believe this is interpolation.

    Example:

    Given: 25th = $100,000
    Given: 50th = $150,000
    Given: 75th = $200,000
    Given: 90th = $300,000
    Input: 60th
    Output: 60th = X

    Here is the vba function that calculates the percentile. I greatly appreciate any help!

    Please Login or Register  to view this content.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: VBA Function that interpolates a value from known percentiles 25th, 50th, 75th and 90t

    Formula?

    A
    B
    C
    1
    Pctile
    Value
    2
    25
    100,000
    3
    50
    150,000
    4
    75
    200,000
    5
    90
    300,000
    6
    7
    60
    170,000
    B7: =PERCENTILE($B$2:$B$5, PERCENTRANK($A$2:$A$5, A7, 12))
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    11-02-2016
    Location
    Lexington, Kentucky
    MS-Off Ver
    2013
    Posts
    2

    Re: VBA Function that interpolates a value from known percentiles 25th, 50th, 75th and 90t

    Wow that works great! And much simpler! Thank you for the quick response

    Ben

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: VBA Function that interpolates a value from known percentiles 25th, 50th, 75th and 90t

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] 90TH Percentile
    By jodily in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-24-2018, 01:55 AM
  2. [SOLVED] Excel VBA: Select 50th Row from the top of the sheet
    By clprdctn in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-08-2016, 11:43 AM
  3. Scores and Percentiles - IF or VLookup Function
    By newbie4 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-12-2012, 03:15 AM
  4. Percentiles and scores IF Function
    By newbie4 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-08-2012, 07:46 PM
  5. Excel 2007 : 90th Percentile
    By SDHAKE in forum Excel General
    Replies: 3
    Last Post: 02-20-2012, 06:07 PM
  6. Function similar to TREND that interpolates lin. between data poin
    By Erik Thorsteinsson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-14-2006, 12:00 PM
  7. [SOLVED] percentiles?-How does excel calculates the percentiles worksheet function?
    By Agnes Goris in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-14-2005, 11:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1