# Thread: How much processing power do I need?

1. ## How much processing power do I need?

How much processing power do I need to complete the attached spreadsheet quickly?

There's a workbook attached with 826k+ rows of customer account numbers in column B. In column A I'd like a formula for each of those records that counts the total occurrences of each account number. Some Account numbers will appear in the list multiple times for those customers that have had multiple transactions.

The file is big, about 7mb, so I've attached a compressed version of 3.5mb. On my work pc I turned Calculation to Manual and then copied the formula in A2 down to row 826638. When I calculated the spreadsheet I went to lunch and it was still calculating several hours later. I aborted the process with ESC. The pc is over four year old, but it's got 16Gb of ram and an Intel Xeon CPU E5503 @ 2.00GHz (running 64-bit XP). I figured a pc with that much ram should have been able to conquer the task.

I recently bought a pc for home so I thought I should try this task there to see how quickly a new faster processor might work out the results. The new pc runs an Intel i5-4440 CPU @ 3.1GHz with 12Gb of ram (running Win 8.1). But after I copied the formula down the column and hit F9, the pc appeared to be chugging (or perhaps choking) about as fast as my pc at work.

A web search of CPUs shows that the Xeon E5503 processor has cache sizes (L2 & L3) of 512kb and 4mb, while the 4440 runs caches of 1mb and 6mb. I think both chips have an onboard L1 cache of 256kb. But apparently the cache size isn't enough or maybe that's not the problem.

So my question for this post is: How fast does your pc run this task? If you can complete this task in under 15 or 30 minutes, I'd really like to know what processor and configuration you are running. If you try run this task, you should first save all other work and close other apps as you may get an "out or resources" notice.

2. Arcturus,

Currently in process on my Desktop with i7 Haswell (4 cores 8 Threads) and 12 Gb Memory, Win 8.1 Update 1 Pro 64-Bit, Excel 2010 32 Bit. Processing about 2% per minute.

3. First, you have to amend your formula from
=COUNTIF(\$B\$2:B826638,B2) to =COUNTIF(\$B\$2:\$B\$826638,B2)
or when you copy down you will run out of resources because the upper limit of the range grows as you copy down.

I limited to checking the first 50,000 numbers over the entire 826638 which took 1 min 25 sec w/ Core I7 OC to 4.2 Ghz at 100% load, 12Gb RAM, Win 7 Pro 64 Bit, Excel 2010 32 bit. It sent my Temps on all cores to 59 degrees with Corsair 100i liquid cooler.

Congrats, a home brewed stress test benchmarking tool!

4. Access based solution here: https://windowssecrets.com/forums/sho...ower-do-I-need.

Takes just a few seconds.

5. Arcturus,

Ok the test finally finished w/o error in 2:25 on my machine. Note: I'd made the change to the formula noted by Maud before copying the formula and running the test.
Looks like Rui's solution is the way to go! HTH

6. Hi RG/Arcturus

You don't need to use Access.

I opened the file, and created a simple pivot table on an empty sheet.
I used entire Column [B] Account# as my data source.
I chose Account# as my choice for Row labels, and Count of Account# for my Values.
The pivot table was created instantly on my Corei7 Win7 laptop i.e. less than 2 seconds.
I could then use filters to examine the data.
For example, three accounts had 4 entries, 1 account had 6 entries (120435).

zeddy

7. Can't argue with <2 seconds in Excel. Out of curiosity I moved the data to an array processing language (called APL) and the same calculation was virtually instantaneous.

#### Posting Permissions

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