Computer advice needed, Heavy Excel load

[Darth Bobo]

Anyone who have experience with working in large excel sheets and what kind of build can handle it well?

Specifically I'm analysing around 250.000 lines of data and nunning around 1.000.000 sumif and countif formulas in a sheet and will need to Pivot the dataset in the same excel file in as much as 5 or 6 seperate pivot tables and would rather not have to wait hours (literaly with my current system).

What kind of system can cope with this?

 

[tonytnnt]

I'm assuming you're using Office 2010. This may be a job where you need to try and run it on a Core i7-2600. Also do this:

Click File | Options

Click Advanced.

Scroll down to the Formulas section.

There is a check box "Enable multi-threaded calculation", and if this is ticked, you can specify the number of calculation threads. By default, the check box is ticked, and all processors will be used.

What are the specs of your current system?

 

[Darth Bobo]

I'm assuming you're using Office 2010. This may be a job where you need to try and run it on a Core i7-2600. Also do this:



What are the specs of your current system?

Actually it's Excel 2007 and I'm running it on an overclocked E6600 (3.2GHz), system in sig otherwise.

I'm unsure if the issue is the nature of the formulas as the biggest of the "countif" formulas are checking approximately 615.000 cells in order to calculate and I want to run this kind of calculation 820.000 times for the sheet before i Pivot?

 

[cyclone3d]

I would go to x64 and up your RAM to at least 4GB.

I would also go to a newer system as the abysmal RAM throughput on your system is probably also a limiting factor.

The more threads the better.

 

[cbliss1000]

Just outta curiosity, what numbers are you crunching? Excel is pretty inefficient in some of its calculations: could you use a stats program like R to do whatever you're doing?

 

[Darth Bobo]

Just outta curiosity, what numbers are you crunching? Excel is pretty inefficient in some of its calculations: could you use a stats program like R to do whatever you're doing?

I'm crunching transaction data from the last year. I don't really have access to statistical tools or much other than office.

It should be said that I'm trying this on my home PC as it's faster than my work computer, I'll see if I can gain anything from running the sheet trough our citrix enviorement today. Otherwise I try and request proper hardware.

 

[cbliss1000]

R is free and open source, so don't let price scare you away. Should be much faster, though the learning curve may be an issue (lots of R forums out there that'll help noobs out).

I really recommend doing it that way, especially if you need accurate results: Excel makes lots (and lots) of errors on large datasets. True story.

 

[Darth Bobo]

It should be said that I'm trying this on my home PC as it's faster than my work computer, I'll see if I can gain anything from running the sheet trough our citrix enviorement today. Otherwise I try and request proper hardware.

Just as a followup on this I got the data calculated yesterday running in via our Citrix enviorement, it took a little more than 3 1/2 hours to calculate the 820.000 Countifs formulas in the sheet.

I'd rather not have to find the time to learn new programs for data analysis at this is the only data I'll be crunching even if I risk having to do it every month. I'll probably be looking into how much approx $2.000 (max is DKK12.300 ex. VAT) can do for this and see if that will be the way to go.