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.

SUMPRODUCT	159,7185
SUMIFS	49,99954
SUM+OFFSET	1,520338
Prefix sum	0,136628
Prefix sum fixed	0,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]

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