slow calculation of rank and percentile in Excel 2007

  • Thread starter Thread starter jepalmer
  • Start date Start date
J

jepalmer

I have a spreadsheet that was created using Excel 2003 on XP
computer. On a win7/Excel 2007 computer, the rank and percentile
calculations that used to take 1 to 2 minues, now takes 1 to 2 hours.
To test whether it was the spreadsheet or not, I started with a clean
worksheet in 2007 and created a list of numbers using rand() and then
copied and paste special/value to get a list of 1000 numbers.

Using the same Win7 computer with both 2003 and 2007 Excel loaded, I
did some rank and percentile calculations.


In Excel 2007
No. Points//Calculation Time (min:sec)
10//0:08
50//0:32
100//1:03
500//5:10
1000//10:26

In Excel 2003
No. Points//Calculation Time
10//0:01
50//0:01
100//0:01
500//0:01
1000//0:02

I also tried the original spreadsheet in another computer with win7/
excel 2007 and it took anywhere from 1.5 hrs to 3 hrs to calculate a
rank and percentile of 5114 numbers, depending on what else the
computer was doing at the time. The 1.5 hrs calculation time was with
nothing else active.

Is anyone else having these types of calculation times? Any
suggestions on how to speed up the 2007 calculations (Excel set to use
multi-threaded calculations)

Right now my workaround is to save file/open in 2003, run rank and
percentile/save file/reopen in 2007.

Thanks for the help.
 
I tried using the RANK() and PERCENTILE() functions on 5000 random
numbers using Excel 2007 and Win 7 and it takes 0.15 seconds on my
system for 5000 calls to RANK() and 5000 calls to PERCENTILE().
 
I tried using the RANK() and PERCENTILE() functions on 5000 random
numbers using Excel 2007 and Win 7 and it takes 0.15 seconds on my
system for 5000 calls to RANK() and 5000 calls to PERCENTILE().
Click to expand...


I'm using Rank and Percentile from the Analysis ToolPak. Would that
make a difference?
 
I'm using Rank and Percentile from the Analysis ToolPak.  Would that
make a difference?
Click to expand...

It gets "cursiouser". If I turn off multi-threading, the original
spreadsheet runs 'Rank and Percentile' calculation in 2:30 and the
test spreadsheet 'Rank and Percentile' calculation in 5:15. No speed
demon, but still faster than with multi-threading turned on.
Any suggestions on testing multi-threading or CPU?
Thanks.
 
Back
Top