Excel Percentile function with large data sets

[AngelB]

Hello all

I originally posted this in the office forums, but I have been advised that my problem could be solved using vba.

I am using excel 2003 (SP2). I am exporting data from some mapping software to excel, where I would like to be able to use the percentile function on a particular column of the data. Unfortunately there are often more than 65536 rows of data meaning that it cannot all be pasted into excel in one range. I could paste the data into more than one sheet or into a few different ranges on the same sheet, but then I don't know how I could perform the function. I have thought about exporting to an access database, before exporting to a recordset in excel and performing the function on the recordset, but I couldn't get the percentile function to work that way. Can anyone think of another way I can handle the data so I can get this function to work on it? Interestingly, the Excel help states for the percentile function that:

If array is empty or contains more than 8,191 data points, PERCENTILE returns the #NUM! error value

I find this a bit odd, since when I have had smaller data sets in excel (30,000+ rows) the percentile function has worked just fine on the relevant column.

Many Thanks

Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[macropod]

Hi AngelB,

The same incorrect error message appears in the Excel 2007 help file - I've just tried it with 262144 data points and a value was returned. Maybe Excel 2007 is what you need. Alternatively, you could have a macro read all your data into an array, then use the WorksheetFunction.Percentile method on the array to return the value you're after. There is no need to actually load the data onto a worksheet to do this.

Cheers

[MS MVP - Word]

 

[xlbo]

Please see post in MSOffice forum. Perfectly possible to do in versions of excel under 2007 using a named range

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question