Is this PC spec good enough for large spreadsheets and complex formulae

[herbiefish]

Hello,

Would anyone be able to advise if the following spec PC should be adequate for complex excel spreadsheets and large volumes of data linking to external workbooks?


Processor Intel(R) Core(TM) i3-6100 CPU @ 3.70GHz, 3700 Mhz, 2 Core(s), 4 Logical Processor(s)
Installed Physical Memory (RAM) 8.00 GB

A few of us in the office are really struggling with big workbooks containing lots of formulae and our IT team have said they think the PC spec is good enough and we should be optimising our spreadsheets and formulae instead. We need the files designed the way they are but I am going to download FastExcel V3 and see what it returns. I would just like to know if the spec is indeed good enough for fast complex excel calculations and large workbooks or if we are being fed bull by IT?

Thanks

James

[TMS]

Depends on your definition of big. Lots of sheets? Lots of columns and rows on many, if not all of the sheets? Heavy use of formulae? Formatting? Conditional Formatting?

Do you have formulae going down the sheet(s) to prepare the rows for data entry?

Do you make use of Array Formulae? Do you use full column references in them, or in any of your formulae?

If the workbooks are big, is it because there is a lot of data or because they are bloated?

Gut reaction is you could probably do with an i5 or an i7. 8 Gb of RAM is good but you won't be using it unless you install the 64 bit version of Excel. But then you might have compatibility issues unless everyone migrates. And there are limitations with the 64 bit version if you are using VBA.

[herbiefish]

Thanks for the quick reply.

I can give you one specific example.

I have 4 separate sheets in one workbook that contain postcode data for England & Wales. The data was too large for one worksheet so I split it into 4 worksheets. I then have a second workbook that has c.30k rows and 25 columns of static data in. Within this workbook I have a couple of IF conditions referencing data in the same sheet and then I have an index and match formula looking up the postcode from from the postcode workbook and returning the county. This formula is in every one of the 30k rows and this one is the one that is causing the biggest problem. It takes well over 10 minutes to open the file or to refresh the formula. I have taken to hard coding the formulae where I can but I'd really like to keep them in so that I can be updating the data when I need to.

[Norie]

Can you post an example of the formula you are using?

[herbiefish]

Sure, here it is.

=IFERROR(INDEX('[All E&W Postcodes.xlsx]England1 postcodes'!$B:$B,MATCH(G5,'[All E&W Postcodes.xlsx]England1 postcodes'!$A:$A,0)),IFERROR(INDEX('[All E&W Postcodes.xlsx]England2 postcodes'!$B:$B,MATCH(G5,'[All E&W Postcodes.xlsx]England2 postcodes'!$A:$A,0)),IFERROR(INDEX('[All E&W Postcodes.xlsx]England3 postcodes'!$B:$B,MATCH(G5,'[All E&W Postcodes.xlsx]England3 postcodes'!$A:$A,0)),IFERROR(INDEX('[All E&W Postcodes.xlsx]Wales postcodes'!$B:$B,MATCH(G5,'[All E&W Postcodes.xlsx]Wales postcodes'!$A:$A,0)),"POST CODE NOT ACTIVE"))))

[Norie]

So you are using entire column references, that's could be why calculation is so time-consuming.

You should probably look into using dynamic named ranges to restrict the references to only rows with data.

[herbiefish]

Thanks. Not used a dynamic version of a name range before so will look into it.

So would a much better spec PC help in this situation? It is still quite slow on other smaller tasks too. Basically they have just been bought as a n upgrade to our existing ones but they just don't appear to be that much of an upgrade!

[TMS]

You could convert each of the data tables to Structured Tables. Then Excel will manage the Dynamic Named Ranges for the table as a whole and each of the columns. You can use Structured Table References in your lookups. Check for, and remove, any unnecessary Conditional Formatting ... or formatting in general.

I'd try that before looking to upgrade your PCs.

As for other workbooks, they may be suffering similar performance issues for the same reasons. So, again, analyse and improve what you have first.

[herbiefish]

Thanks. for the help. I'll look into it.

[Richard Buttrey]

One further thought. How often do the counties change for any particular post code. I suggest rarely.

Once you've used the formula to find the county - (using a dynamic range name as has been suggested), is there any reason why you retain the formulae? Why not convert them to constants. Surely you only need the formula when adding a new post code. In similar situations I generally keep a master copy of the formula on row 1 so that when it's next needed a macro can simply use it and then after it's been used convert it to a hard value.

[CK76]

Looking at the spec, it's good for most standard operations (excluding PowerQuery/PowerPivot).
Good workbook and formula design should allow you to handle things comfortably.

[herbiefish]

That's exactly what I've had to do, it's just a pain because we have lots of different types of workbooks with different data on that require postcode matching and many other link and references to large external books. So we go through the same problem every time we want to use the data.

I have since tried dynamic named ranges and it did speed up the process but was still slow and caused the machine to display not responding.

I am quite surprised that a better processor wouldn't increase the speed that these workbooks perform at but I'm certainly not going to argue with you guys.

Appreciate all the replies :-)

[Richard Buttrey]

Thanks for the quick reply.

I can give you one specific example.

I have 4 separate sheets in one workbook that contain postcode data for England & Wales. The data was too large for one worksheet so I split it into 4 worksheets

If it's only the County that you need then you don't need to search all the ~ 1.7 postcodes in the PAF File
All you need is the outgoing DISTRICT part of the postcode file. It's a long time since I used any PAF files but I'm assuming you can request a District only file rather than use the whole postcode file.

Even if you can't get just a District file that showed City-Town / County I think I'd create a simple macro to first extract the Outgoing City/-Town/County fields and then Remove the duplicates and then use that reduced file instead of your 4 files.

[tim201110]

So you are using entire column references, that's could be why calculation is so time-consuming.

You should probably look into using dynamic named ranges to restrict the references to only rows with data.

the worst thing is link to another file.