Excel Workbook Causes PC Crash

[Ad_B]

Hi Everyone!

I have managed to do something I never thought I could (or was possible).

I have created an Excel Workbook that on opening/using crashes the PC.

It contains almost 350,000 formulas - 4000 lines with 70 formulas on each, plus other worksheets!

I am using a works PC, so traditionally its poverty spec'd.

More memory has been added, now 8GB RAM. But the processor is the issue.

Current spec:

Processor: Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz
RAM: 8.00 GB (7.90 GB usable)
System: Windows 7 64-bit OS

What is the best processor to have to avoid this issue and any further potential issues?

Help appreciated.

Thanks

 

[RoryA]

If it doesn't work with that spec, I'd rethink the formulas. Note: it is quite likely that Excel cannot use most of the RAM you have added.

 

[Ad_B]

Only about 3GB of the ram is being used at any time. I didn't think that was the issue personally, but the guy on IT dept wanted to try that as a first option.

Will the spreadsheet not work with any spec'd PC? I'd rather not remove the formulas if I can avoid it. I spent a few weeks designing/building the sheet. I cannot save any changes I make at the moment so would have to start from scratch.

 

[RoryA]

Impossible for me to say but if it doesn't work on that spec I would strongly suggest you need to rethink your design. I'm not saying you need to remove the formulas but perhaps they can be made more efficient.

 

[Ad_B]

That could take some time - will see what is suggested here.

I will try it on a higher spec'd machine and see if there is any success - if not, I guess its back to the drawing board...

Thanks for your help

 

[Ad_B]

I assume VLOOKUP isn't the most efficient formula I could use? I have used INDIRECT, INDEX and MATCH functions also.

Whats the most efficient formula to use in this instance?

 

[RoryA]

INDIRECT is very bad because it is volatile so recalculates whenever anything gets calculated, even if none of its inputs have actually changed.
VLOOKUP is generally less efficient than INDEX and MATCH, especially if you use VLOOKUP to return data from multiple columns for the same lookup value - in that case it is far more efficient to use MATCH to get the match position once and then use that result in several INDEX formulas.

 

[Ad_B]

Ah - I didn't realise that with INDIRECT. Will try and clear that and use MATCH and INDEX instead.

 

[RoryA]

Probably worth having a read of this page: Volatile Excel Functions -Decision Models
and that site in general in fact.

 

[Ad_B]

OK - thanks