percentrank.inc

[Reboshua]

I have just under 600k rows of data, all values, no formulas. I am trying to do a percentrank.inc for one column of data and it's taking 20 minutes to do it.

I have an absolute beast of a machine - AMD Ryzen Threadripper, using all 32 cores, with 32gb of 2666mhz ddr4.

How can it possibly take that long to calculate one column of data?

 

[shg]

It has to sort 600,000 rows 600,000 times ...

 

[Reboshua]

shg, thanks for responding - can you say more? It doesn't feel like it should take 20 minutes.

https://www.tomshardware.com/reviews/amd-ryzen-threadripper-1950x-cpu,5167-12.html

 

[shg]

I get that you have a fast PC. The formula is sorting 600,000 rows in 2 milliseconds (20*60/600000); that sounds pretty fast to me.

Then you do it 600000 times.

You might try sorting by that column first. Some sort algorithms are faster when the data is sorted, or nearly so. Some aren't.

 

[DRSteele]

I put numbers 1 through 600000 in a column and the percentage formula in another: it took 17 minutes.

 

[Reboshua]

I guess what I don't understand is why it has to sort 600,000 times. I can calculate 1 cell in the range and it doesn't need to know what every other cell is going to be

(I've tried it sorted descending as well and no different, unfortunately.)

Thanks

 

[shg]

I guess what I don't understand is why it has to sort 600,000 times.

That's how PercentRank finds the position of a value in a dataset.