# Best computer spec for Excel power user



## gottogofish (Jun 10, 2013)

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 (Jun 10, 2013)

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. (Jun 16, 2013)

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. 


SUMPRODUCT159,7185SUMIFS49,99954SUM+OFFSET1,520338Prefix sum0,136628Prefix sum fixed0,042462
 
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 (Jun 17, 2013)

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


----------

