+ Reply to Thread
Results 1 to 4 of 4

Macro to calculate what percentile a value is in

  1. #1
    Registered User
    Join Date
    06-14-2011
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    2

    Macro to calculate what percentile a value is in

    Hi,

    I have a column of data in sheet 1, all numbers.

    In sheet 2, I have another number, call it X in a cell.

    I need to write a macro that calculates which percentile X finds itself in (relative to the data in sheet 1) and spits it out like this "1st Percentile" or "2nd Percentile" etc.

    Have no experience at all with macros, would greatly appreciate any help.

    File attached,

    Thanks
    TR
    Attached Files Attached Files

  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: Macro to calculate what percentile a value is in

    See the PERCENTRANK function.

    The value tested must be between the values listed in array (5.64 to 170.62), so your example value (5) isn't going to work.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    06-14-2011
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Macro to calculate what percentile a value is in

    Quote Originally Posted by shg View Post
    See the PERCENTRANK function.

    The value tested must be between the values listed in array (5.64 to 170.62), so your example value (5) isn't going to work.

    Was aware of Percentrank function, but would like to know how to do it in a VBA.

    In the file I attached, all I want to do is have the macro calculate the percent rank for the value in cell C3 in the 2nd worksheet, and then paste the percentrank value into another cell in that same 2nd worksheet.

    I started writing the following, and then realized I had no idea how to finish it and paste the value spit out by the worksheet function to a cell in the 2nd worksheet.

    Sub Rank( )
    Sheets("Main Data").Select.Range ("C3:C172")
    Application.WorksheetFunction.PercentRank ("C3:C172", ...)

    Not sure how to finish this, it's probably not even started right, but I know that we need to use the worksheetfunction.percentrank.

    Thanks much for any help,
    TR
    Last edited by Toneranger; 06-15-2011 at 11:47 PM.

  4. #4
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Macro to calculate what percentile a value is in

    Hi,

    In your example the required VBA is

    Please Login or Register  to view this content.
    However, as pointed out by shg, the example you have posted will generate an error.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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