Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Well, it's been 10 minutes now and Excel 2007 has not yet calculated a Rank and Percentile on about 5000 values. The same workbook in Excel 2003 on a different computer, it took less than a minute (I forgot to time it).

    Any Idea why? I'm using win7 and the other computer is on XP, if that is germane.

  2. #2
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I've done some more testing using attached spreadsheet. Same Computer, a number set generated using rand() and then copied and paste special/value.

    Rank and Percentile Calculation

    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 was with nothing else active.

    Is anyone else having these types of calculation times?

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

    Thanks for the help
    [attachment=90096:rank and percentile test sheet.xlsx]
    Attached Files Attached Files

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I just tested this on my Laptop, running Windows 7 Ultimate and Office 2003, 2007 and 2010.
    In all cases it generated the Rank and Percentile data using the Add-In from your 11000+ rows of random data
    in 20 to 23 seconds.

    That is with 4GB Ram.
    Andrew

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks.

    Question: could it be a question of multi-threaded calculations? Did your Excel run a multi-thread? How does one tell if Excel is running in multi-thread mode?

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Check under Excel Options, about 3/4 of the way down the Advanced section.
    My computer is by no means fast, but took about 25 seconds or so.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    There's pretty much nothing to choose between the two on my machine (which is about what I'd expect)
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    New Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I've been lurking for quite while, but have never posted before. For what it's worth, I downloaded the file in jepalmer's post and ran it both on Excel 2003 and 2010 on my HP computer with an i7-920 processor and 8 G Bytes RAM. I could not get an accurate timing as I was just watching the second hand on my wall clock. It appeared to me that both versions of Excel finished in less than two seconds. If this sheds any more light on this issue, great!

  9. #9
    5 Star Lounger petesmst's Avatar
    Join Date
    Dec 2009
    Location
    Cape Town, South Africa
    Posts
    790
    Thanks
    38
    Thanked 43 Times in 33 Posts
    Ditto to Al Zeiner's results with my system (See my signature below).
    (My Setup: Custom built: 4.00GHz Intel Core i7-6700K CPU; MSI Z170A Gaming Carbon Motherboard (Military Class III); Win 10 Pro (64 bit)-(UEFI-booted); 16GB RAM; 512GB SAMSUNG SD850 PRO SSD; 120GB SAMSUNG 840 SSD; Seagate 2TB Barracuda SATA6G HDD; 2 X GeForceGTX 1070 8GB Graphics Card (SLI); Office 2013 Prof (32-bit); MS Project 2013 (32-bit); Acronis TI 2017 Premium, Norton Internet Security, VMWare Workstation12 Pro). WD My Book 3 1TB USB External Backup Drive). Samsung 24" Curved HD Monitor.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •