Usage of Percentile WorkSheetFunction...

[deepthiv]

i am using Excel Object API in VB.NET

Dim X1 As Object

X1 = CreateObject("Excel.Application")

Dim res As Double = X1.WorkSheetFunction.Percentile(dblData,p)

here is dblData is array of type double which has recordset column values,

There is an exception saying. "Unable to get the Percentile property of the WorksheetFunction class"

i have added "Microsoft Excel 9.0 Object Library"

am i missing anything? Can some one please help

Regards,
Deepthi

 

[Jon K]

In Access 2000, the following code can correctly return res = 1.9

Private Sub Command0_Click()

   Dim X1 As Object
   Dim dblData(3) As Double
   Dim p As Single
   Dim res As Single
  
   Set X1 = CreateObject("Excel.Application")
   
   dblData(0) = 1
   dblData(1) = 2
   dblData(2) = 3
   dblData(3) = 4
   p = 0.3
   
   res = X1.WorkSheetFunction.Percentile(dblData, p)

   MsgBox res
End Sub

I don't have VB.NET for testing. Hope it helps.
.

 

[deepthiv]

thanks jon.. for ur reply

but in VB.NET i am getting error saying

"Unable to get the Percentile property of the WorksheetFunction class"

for

X1.WorkSheetFunction.Percentile(dblData, p)..

i am breaking, can some one please...

 

[Jon K]

Instead of using the Excel Percentile function, you can try using a user-defined function.

I have attached a sample database. You can open the form, select a percentile and click on the command button to calculate the specified percentile using VBA. I have also included the Excel function on the form for verification.

The VBA function works on 0-based 1-dimensional arrays. I have also included a subroutine in the database for sorting values in a 1-dimensional array, in case the values in the 0-based array are not given in ascending order as in my example.

Hope the VBA function can be adapted for use in VB.NET
.

 

[boblarson]

Also, this belongs in the Visual Basic.NET section of this forum and not in the VBA code section.

 

[deepthiv]

Thanks a lot jon..

This is gr8 code..

I have to calculate percentile on entire column which has nearly 2000 rows

and each value has nearly decimals upto 6 to 7 points .

and now i am getting differences in percentile values when calculated from excel percentile formula and using VBA 'MyPercentile' code.

i need correct values (as excel shows) using VBA Function, wht should be doing?? do i need to truncate or round each individual value??


Regards,
Deepthi

 

[Jon K]

I have added many decimal places in my example and changed res from Single to Double so as to get more decimal places in the value returned. I have tested it in Access on two machines (Win98SE and WinXP SP2).

In all my tests, the function MyPercentile() returned exactly the same results as the Excel Percentile function did e.g. in the attached screen shot, they both returned 43.724388456 for the 68th Percentile.

Though slightly different formulae exist for calculating percentile, I'm convinced the formula that my function used is the same one that the Excel function uses.

Not sure if it's a VB.NET problem.
.

 

[deepthiv]

One more info...

here i am reading excel column values into recordset -(output of ' select col1 from table1') and

reading record set value (till rst.Eof) in to array and using this array in Percentile function....

any help on this?

regards,
Deepthi

 

[deepthiv]

yes true jon..

it's correct.. but why it's deviating for 2000 + records??

ok...'!!!

can we think in this way...

Is there any sql query that can find nth percentile of given column...

( here my database is excel and want to display percentile in web page)

not sure why i am facing this problem. am i missing some thing...

Thanks,
Deepthi

 

[Jon K]

If the data is in a table, you don't need to use an array. You can open the table in a recordset and move quickly to the two records from which the percentile is calculated.

I have added a table and a form in the database. When you click on the button, the value 43.724388456 for the 68th Percentile will be returned from an ADO recordset. This value is the same as in the screen shot in my previous post.

In the code I declared p as Variant:-
Dim p As Variant
p = 0.68


I found that if p is declared as Single, I would get a slightly different result: 43.7243887324 for the 68th Percentile

If you have declared p as Single or Double, you can try declaring it as Variant to avoid conversion difference.
.

 

[deepthiv]

Thanks lot jon....

It worked for me...

Thanks again....(now i have only minor differences)

 

[skidmore]

Jon K,

Several years ago you posted a sample Access database that calculated the number in a recordset that corresponded to a particular percentile; in your example the number 43.7 corresponded to the 68th percentile.
(Calculate Percentile from recordset Access 2000.zip)

Do you happen to have code that calculates the opposite; that is given the number 43.7 in a recordset, calculate the percentile this corresponds to (i.e., the 68th percentile).

Also, I understand that there are two expressions for the percentile calculated. One is the value of 43.7 is equal to the 68th percentile, the other is that the value of 43.7 is equal to or greater than the 68th percentile. It is the latter calculation that I need.

Thanks.