Hardware for Optimizing Calculations

[VL00kup]

Hello,

I was hoping the community might be able to help me figure out what tech specs in a new computer could help me optimize the performance for an Excel file I use to produce a twice/weekly report/newsletter as a side business.

The file is ~100 Mbs and growing. The file essentially has 3 large flat files with calculations and look-ups across that drive a lot of the file size (in parallel, I am looking at ways to reduce the calculation complexity by structuring this better). I also have a macro that pulls and generates about 10 output sheets which I then print to PDF and publish.

The file is manageable enough, but hitting F9 can take 10+ minutes to update and it creates significant overhead in preparing the report. I am willing to invest in better hardware to streamline the update process - where should I put the money to optimize the Excel? One specific question I have is if a desktop might be inherently more effective than a laptop (a laptop would be preferred, but I am willing to compromise)? Any other ideas?

Thank you!
Aaron

 

[mole999]

multi core processor, 64 bit architecture, 64bit office, plenty of ram and fast hard disk.

32bit excel can only access 3Gb, 64bit goes much much more.

But you might do more by looking at your current code first

 

[VL00kup]

Thanks!

 

[SpillerBD]

Using the PowerBI tools may have advantages too. You have time spent in the Get&Transform and/or Power Pivot space but avoids the calculations on the sheet(s). VLOOKUP or any array function, sucks for big-sheets. Huge improvements using DataModel or Get&Transform.
You could also consider VBA to do the calculations for specific columns/Rows/Cells and just populate those cells with the values.

I've been as bad as 40minutes for an F9, but after some re-work got it down to about 2.

 

[VL00kup]

Impressive. I'm not familiar, but I will check that out now.