Percentiles using VBA 1

[zamuro]

I am trying to generate a function to calculate percentile from a list of random numbers in Excel. However I do not want to use the formula WorksheetFunction.Percentile(queue(), 0.5) because I have too many numbers and generating a queue() will take too much memory.

What I need is an approximation in which I can maintain a limited number of variables (a sum, a rank, etc) and output the percentile as approximate as posible.

thanks

 

[zamuro]

I forgot to mention that the data is not sorted therefore I cannot use the N forula for Percentiles and I can't sort the data either because it will also take much memory and time

 

[MichaelRed]

Not in Excel, s it is ALWAYS limiting on the input data set (using the builtin functions). Using the search feature on the SITE should return a thread (or so) in MS. Access(VBA) and / or VB which will generate the Percentile rank of the values in a dataset (table). It should not be difficult to adapt to Excel VBA, as the only real difference would be the range references to replace the fields.




MichaelRed

 

[zamuro]

Sorry Michael but I was not able to follow you. could you explain me what you wrote with an example?

thanks

 

[MichaelRed]

Wrote what? The above post?

Excel limits the number of elements it will generate a percentile rank for. Per (an OLD reeference), this is (was in the days of yore) 8192. It may be different in newer versions. I haven't used Excel for generating a percentil rank for a looooooooooooooooog time, primarily because of the limit. Since I work ammost exclusively with databases, I used some info found on one of the MS Access of VB forumns here (Tek-Tips) to write a custom version for a project. That version uses a table expecting (input) arguments specifying the Table and field within the table as well as other parameters, and returns the percentile ranking of one of the arguments.

I believe the routine (or some adaptation) was posted in the forum where I go the original info re the deffinition of Percentile. If YOU search the fora and find the routine, it should be reasonably easy to adapt to your specific purpose.



MichaelRed

 

[zamuro]

Michael thank you for your help. However I still haven't been able to solve my problem.

Imagine I have a list of numbers (many)

1324.34
3432.43
43543.32
11.343
444.213
...
...

What I need is to find the 10,50 and 90th percentile of the list without storing them in a queue or an array and without sorting them first (because there are too many numbers and sorting them will consume much memory and time)

I need to mainatin a few variables with a sum, a product or something that could then be approximated to a percentile. I NEED SOME MATH....

 

[MichaelRed]

[qoute zamuro] " ... without storing them in a queue or an array and without sorting them first ... " [/quote]

Sorry, but that is beyond the routines I refer to and my ability as well. Percintile (rankiing) refers (perhaps roughly) to the percent position within an ordered list. This seems to imply the existance of the list (although reasonable approximations - e.g. arrays, recordsets, etc. are genedrally acceptable substutiuons), and their ordering. Perhaps others can / will offer their (different) help.




MichaelRed