Desktop PC to Crunch Excel

[drew]

So, our jobs require constant data pull/formulation on huge spreadsheets (100MB/206k rows and 90+ columns), and the weak ass laptops can't handle them anymore when you try to do some significant data collection/etc.

It's a pain in the ass, but, I'm trying to look at a cheap solution desktop.

The primary thing is the CPU. I'm sorting out whether a dual core, or quad core is what we need. I have Office 2007, so it's multi-threaded.

That said, the rest of the bits are fairly inconsequential.

Ram is ram, mobo is mobo (onboard graphics since it's sole purpose will be excel). etc.

I think I would almost spend a little more on the processor, and skimp on the other parts.

[thesameguy]

The multi-core-ness of Excel in practice depends on what exactly it is you're doing. When a file loads, Excel creates a table of what they call "calculation trees" and assigns one tree per core, more or less. So, if you're totaling three columns of numbers you have three trees and Excel can use three cores. But, if each column also adds the total from the previous column, then you only have one tree and anything beyond one core is wasted. If you're doing the same thing all the time, then it makes it easy to see where the sweet spot is. If you aren't, then you might as well buy the best you can afford. :)

I think the absolute cheapest solution here is a Dell XPS 8700 purchased from the Dell Outlet. I don't think there is a way to get more computer for less. Typically you're looking at six or seven bills for a high-end i7, so basically you get the CPU, then everything else for about $300. :) This is a common configuration:

•Core i7-4790 3.6GHz Processor
•8GB DDR3 Memory
•1TB 7200 RPM Hard Drive
•1GB NVIDIA GeForce GT 720
•16X CD/DVD Burner
•WiFi + Bluetooth 4.0
•Keyboard + Mouse
•12-Month McAfee LiveSafe Subscription
•Windows 8.1 (64-bit edition)

that you can reliably get for about $650.

[drew]

Fucking hell. I've got a 4790k in my "big" machine. :lol:

We do a lot of vlookups across multiple sheets (access) that can be a couple 100k rows each. The laptops get to about 20% "calculating" then just shits itself. To give an example, we ended up doing it on my machine (4790/4Ghz + 32GB ram/SSD (if that really matters)), we had to look up/pull data for 22 columns, for 200000 rows, from another 200000 row sheet. The most "efficient" way I got it to go was doing it in 20000 line chunks and it still took an hour to do it all.


I always forget about Dell.. (even if I wanted to actually build one to impress her with my geekdom...)

[thesameguy]

If you have a Windows license, the value for the prebuilt is reduced, but if you need the Windows license the prebuilt will almost always save you $50-$90.

Is it possible the issue you're experiencing is not CPU-related, but RAM? When your machine is doing the calcs, where is RAM utilization at? Have you considered moving to 64-bit Excel to make use of more?

[Kchrpm]

I've run into similar issues, more cores doesn't help you because it has to go along a single path, so you're stuck just waiting.

I have one spreadsheet where it doddles around, maxing out just one core, only using like 50% of available CPU resources, until it gets to a certain spot where it can finally use both barrels, and then jumps to 90-100% CPU usage.

From what I've read there are other formulas that do what vlookup does, but "faster", like index, so that may be worth trying.

With the amount of data you're working with, I wonder if Access might be a better route to go. I have no idea, but it might be worth a shot.

[drew]

Between the two of us, we pull 6-10 100MB spreadsheets a day...

Our laptops are 15 3320s (Probooks) at 2.6. I've added 8GB to each so we have 12gb ram. But, it's not enough for what we do. My big box has no problem, but it's also 5-6x the horsepower.


She's far more knowledgeable about excel/access than I. I don't know if we have many other options besides vlookup, given the nature of what we're looking up.

Fuck, I don't know. Up til 3 years ago, vlookup was my nemesis.

[Kchrpm]

INDEX can do the same thing as VLOOKUP when you stick a MATCH inside of it. See example: https://docs.google.com/spreadsheets/d/1Z9Pn_oU4uPKwewUoVvAtm0M5OJe_2beDGF3b99lWjcM/edit?usp=sharing

Or if the field you're looking at is sorted, it's supposed to be faster to do the VLOOKUP with sorted as TRUE.

But yeah, with the size of the data you're looking at, it may not matter.

Can you remote desktop to the big box?

[thesameguy]

Sounds like 64-bit Excel might be the solution. Running 32-bit software on a 64-bit OS with 12gb RAM still means you're running 32-bit software. ;)

[drew]

Correct. :)

Probably a no-go on the remote desktop, due to the laptops having VPNs. Which is a whole other fucking catastrophe (unrelated rant).

130MB down off vpn, 25MB on. Fuck security!

[21Kid]

we had to look up/pull data for 22 columns, for 200000 rows, from another 200000 row sheet. The most "efficient" way I got it to go was doing it in 20000 line chunks and it still took an hour to do it all.Dockonomics in action!!!! :finger:

That is all I have to add...

[Yw-slayer]

The most expensive processor you can buy and 32GB RAM OR SOMETHING FOR THE LULZ

[Kchrpm]

Another optimization you can do is that, if you're ever looking up the same thing multiple times and then just pulling different columns for it, use MATCH in one column to find the right row, and then use INDEX to just cycle through those columns. For example, instead of this:

VLOOKUP("A",$X:$Z,2,false)______VLOOKUP("A",$X:$Z,3,false)______VLOOKUP("A",$X:$Z,4,false)______VLOOKUP("A",$X:$Z,5,false)

Use this:

MATCH("A",$X:$Z,0)______INDEX($X:$Z,$A2,2)______INDEX($X:$Z ,$A2,3)______INDEX($X:$Z,$A2,4)______INDEX($X:$Z,$ A2,5)

(where the MATCH function is in A2)

This way it only has to do a search through the huge column once, instead of 4 times.

[Kchrpm]

Maybe I should just fly to Jacksonville and make up a consulting fee ;)

[21Kid]

He's worth it!!! Pay him whatever he wants. :D

[drew]

I only pay by the inch. It takes a little longer, but is more satisfying than a lump sum.

...and we have a proper guest room (slash office).

[21Kid]

Is that what you tell the GF?

[drew]

I haven't heard any complaints anyway, I just thought that's the way it worked!

[Jason]

I have a pretty hoss machine at work, but 2010 makes use of like 20% when crunching numbers. Is that because it's 32bit?

[Kchrpm]

No, 32-bit limits the memory use, not the processor use. The 64-bit version can help with what's in RAM vs HD memory swaps, but it still just computes most stuff down the line, in order, on one core. There are some pure number crunching things that it will use all the cores for, but for most of the it does not.

http://excelribbon.tips.net/T011056_Using_More_CPU_Power_when_Calculating.html

Some other tips for speeding up processing: http://www.techrepublic.com/blog/10-things/10-ways-to-improve-excel-performance/

[mk]

(didn't check Kch's links)

Back in the day depth calcs were Lotus only.
Seems that the priciple has not changed.

You need to change your way of thinking.
If you can make the process so that somewhere is +/- loophole it's actually not that.
(any other similar step will do)
Because the spot is static the latter part can go in the future.
Think transputer.

[mk]

I meant keyhole.

If that helps.

[Jason]

No, 32-bit limits the memory use, not the processor use. The 64-bit version can help with what's in RAM vs HD memory swaps, but it still just computes most stuff down the line, in order, on one core. There are some pure number crunching things that it will use all the cores for, but for most of the it does not.

http://excelribbon.tips.net/T011056_Using_More_CPU_Power_when_Calculating.html

Some other tips for speeding up processing: http://www.techrepublic.com/blog/10-things/10-ways-to-improve-excel-performance/

I realize I used a bit of the wrong terminology in my original post. It's rare I have a spreadhseet that is slow due to number crunching, though it does happen sometimes... other times it's just slow to maneuver around in large spreadsheets even though CPU and memory use is relatively low. Just seems like the program isn't efficiently coded or something.

[Alan P]

Now I don't know a huge amount about excel but how big is the spreadsheet? Is it saved and accessed often? I'm thinking lots of RAM and feed it from an SSD would help if it's huge?

[Kchrpm]

I realize I used a bit of the wrong terminology in my original post. It's rare I have a spreadhseet that is slow due to number crunching, though it does happen sometimes... other times it's just slow to maneuver around in large spreadsheets even though CPU and memory use is relatively low. Just seems like the program isn't efficiently coded or something.

Yeah, I don't know about that. It may very well be coded inefficiently, or it's doing some weird thing in the background that mucks up the business.

[Kchrpm]

Get yourself a HOSS laptop: http://www.theverge.com/2015/8/11/9129681/lenovo-skylake-xeon-notebooks-p50-p70

[thesameguy]

Highly disappointed they named them stupid Pxx instead of something awesome like Thighburns Pro or ThinkSterilityX or something.

[Yw-slayer]

Should have called them "REAL MAN PC, BRO 2016".

[21Kid]

DoYouEvenComputeBro Extreme version 2.0

[Yw-slayer]

XXXTR3M33