Excel Macro to calculate PERCENTILE

[nssajith]

Can I get some help for writing a macro for calculating PERCENTILE. The data set will always be in one column but the number of rows will be changing.

I can give an example for the calculation
Data Set Count
10 1
20 2
30 3
40 4
50 5

Lets caluclate the 70th median percentile
O = (1+0.7(5-1)) = 3.8
So the 70th Percentile lies between 3rd and 4th record
70th Percentile = (30 + .8*(30+40))

I would like to calculate the 70th percentile using excel macro the number of records is not constant.

Appreciate help

Thanks

NSSAJITH

 

[ETID]

Not a direct vba solution, but this might point you in the right direction.

=PERCENTILE(A1:A1000,0.2) the 20% quintile
=PERCENTILE(A1:A1000,0.4) the 40% quintile
=PERCENTILE(A1:A1000,0.7) the 70% quintile

...does this help

 

[nssajith]

This is the macro I have currently. I am not able to do this for more than 5400 rows. How can I make it happen?

'MACRO TO CALCULATE PERCENTILE
Dim Arrray(1 To 5000) As Variant
Dim myVar As Double
For Each cel In Range("a1:a5000")
If cel.Value <> "" Then
Arrray(cel.Row) = cel.Value
End If
Next
myVar = Application.WorksheetFunction.Percentile(Arrray, 0.5)
Cells(1, 3) = myVar

 

[SkipVought]

Hi,

For Each cel In Range([A1], [A1].end(xldown))

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[SkipVought]

sorry...

why wouldn't this work?

Sub MyPercentile()
    Dim myVar As Double
    myVar = Application.WorksheetFunction.Percentile(Range([a2], [a65536].End(xlup)), 0.5)
    Cells(1, 3) = myVar
End Sub

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[SkipVought]

or this???

Function MyPercentile(rng As Range, pVal)
    Dim myVar As Double
    MyPercentile = Application.Percentile(Range(Cells(2, rng.Column), Cells(65536, rng.Column).End(xlUp)), pVal)
End Function

where rng is any cell in the column of values and pVal is the percentile value between 0 and 1


Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[nssajith]

Thanks for your replies. My situation is little more complex, I have in column A with values P & R, I want to calculate Percentile for both P& R seperately and number of values for P& R is not constant every time.

Thanks

 

[SkipVought]

Where did "...I want to calculate Percentile for both P& R seperately..." come from???

Thanx for throwing that essential requirement in here FIVE DAYS after your original post.

[red]This is NOT at all a professional post.[/red]

Please get ALL your requirements together and start again!

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[DPlank]

Nails for breakfast, Skip?

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[SkipVought]

At last: something we don't have to blame on Dave!

Dave, doesn't it gripe you when you're playing a game, and all of the sudden, the rules change?

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[nssajith]

sorry for that....I got clarity in this this when I started doing it.

Thanks for the help

 

[SkipVought]

Take a look at SUMIF function. This can probably be done using native spreadsheet functions.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[duncdude]

I was thinking along the same line - although I am attempting COUNTIF - i'll keep trying...


Kind Regards
Duncan

 

[SkipVought]

COUNTIF is probably what is needed, yes.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[duncdude]

Hi nssajith

What do you think? Both SkipVought & myself have come to the conclusion that you can utilise COUNTIF to return the number of cells with a value in. You could then remove 1 from this count and sum those rows. i.e. If COUNTIF returns 54 - meaning the first 54 cells have a value - then set up a calculation to 54-1. This shouldn't be too hard to implement

Please let us know if this is of help


Kind Regards
Duncan