Excel percentile function with multiple ranges 1

[AngelB]

Hello all

I am using excel 2003 (SP2). I am exporting data from mapping software to excel, where I would like to perform some mathematical functions on the data. Unfortunately there are often more than 65536 rows of data meaning that it cannot all be pasted into excel in one range. For instance, I would like to use the percentile function on one of the columns. 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?

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

 

[ck1999]

You could purchase excel 07 it allows 1048576 rows.

Or you could possible use vba. If you want to try this route post in vba forum

VBA Visual Basic for Applications (Microsoft)

ck1999

 

[Gavona]

Before purchasing Excel 07 check that it will do what you want. From Help for Excel 2003:

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

Gavin

 

[AngelB]

Thank you for your replies. Sadly purchasing Excel 07 is not an option, so I'll post a version of this post in the vba forum. Strange that you should mention that the percentile function returns the #NUM! error for more than 8191 data points, since I've regularly been using it on far more rows than that. Am I wrong in my definition of a data point?

Thanks again


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

 

[xlbo]

Hi Gez - should be relatively easy - just use a named range that encompasses all the data points eg.

Range Name: TestName
Range Defintion: =Sheet1!$A:$A,Sheet1!$D$1:$D$10000

Then simply use = percentile(TestName,0.3)



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

 

[AngelB]

Thanks Geoff, that's exactly what I need. I knew of the existence of named ranges but had never had cause to use one til now. Thanks for providing me with the perfect excuse to learn about them. Star for you sir!


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