Best computer spec for Excel power user

[gottogofish]

What will help speed up calculations in a spreadsheet?

I am currently using Excel 2010- 32 bit, with Windowa 7 64bit. The computer is an i5 3.20 GHz and has 8GB of RAM.

Some of my spreadsheets take a long time to re-calculate. I know that changing to Office 64bit will help, but, how significant will it be? Will a work book that takes 90 seconde to calculate go down to 45 seconds? Can a more powerful computer bring that down to 10 secs.?

I do realize I need to improve my formula writing skills, but until then what would be best?

Thanks for your input.

 

[etaf]

out of interest have a look at task manager performance
Control + alt+delete key
performance TAB
also the resource meter

run some excel calculations and make a note of the performance

start in resource monitor - overview TAB

see if the CPU is at 100%
memory usage
Disk usage

see what may be the bottle neck in the performance

 

[J.Ty.]

Hi,

Computer is important, but you can get a lot of boost by investing in yourself. If you learn algorithms, you can often do the same computations orders of magnitude faster.

I have made an experiment.
I have set up 500 000 random sorted numbers between 1 and 1 000 000 in column A, then 500 000 random numbers between 0 and 1 in column B.

Then you have to compute 1 000 sums of numbers from column B, where the corresponding number in column A is between two random numbers supplied in columns C and D.

If you do SUMPRODUCT, it is awfully slow.
If you do SUMIFS, it is significantly faster.
If you take advantage of the fact that column A is sorted, you can do SUM over intervals specified by INDEX and MATCH and speed up everything a lot.
If you use algorithm called Prefix sums it is still a little faster than INDEX and MATCH.
If you know your column A does not change, you can materialize prefix sums doing copy-paste as values. Then prefix sums algorithm is rocket-fast.

All times below are in seconds, on my machine with 2 cores and 4 threads.

[TABLE="width: 238"]
[TR]
[TD="width: 110"]SUMPRODUCT[/TD]
[TD="width: 64, align: right"]159,7185[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]SUMIFS[/TD]
[TD="align: right"]49,99954[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SUM+OFFSET[/TD]
[TD="align: right"]1,520338[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Prefix sum[/TD]
[TD="align: right"]0,136628[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Prefix sum fixed[/TD]
[TD="align: right"]0,042462[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]

As you can see, the last method is over 3500 times faster than SUMPRODUCT and 1000 times faster than the obvious SUMIFS. Imagine how much should a computer 1000 faster than your present one cost.

J.Ty.

 

[Derek Brown]

Be careful about changing from Office 32-bit to Office 64-bit as this was/is not recommended by Microsoft for most people: Choose the 32-bit or 64-bit version of Office - Word - Office.com
Check out the following link (and other page on the same site) for tips about calculations: Advanced Microsoft Excel Consultancy and Solution Development -Decision Models
In particular, look out for Volatile Functions: Volatile Excel Functions -Decision Models