# Macro to calculate what percentile a value is in

1. ## 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

2. ## 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.

3. ## Re: Macro to calculate what percentile a value is in

Originally Posted by shg
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

4. ## 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.

#### Thread Information

##### Users Browsing this Thread

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

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