• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

32bit vs 64bit Excel

F

Fleggy

New Member
I have a fairly powerful machine however Excel seems to struggle with larger files.

I am currently working on a spreadsheet that is 104MB in size with no way of reducing the size down. Am I better to run x86 or x64 version of Excel? Will the x64 help speed up the calculation times?

Thanks
Adam
 
Adam

Does your file use much VBA or UDF's?

Mostly slow calculations are from the over use of Volatile functions (Functions that calculate when they don't need to)
Often spreadsheets can be speeded up dramatically by use of Non-volatile functions in these cases

In regards to size, size doesn't necessarilly equate to slow speed, adding lots of pictures can make a file huge but won't slow it down.
I assume you save the file as a *.xlsb file type ?
 
As far as I know I am not using any UDFs and definitely not using any VBA.

How do I convert my formulas to non-volatile formulas?

What's the difference between a .xlsb and .xlsx??

Thanks for the help
Adam
 
Just as an example, here is one of my formulas from the spreadsheet

=SUM(IF(FREQUENCY(MATCH(CONNECTION_RECORDS_TOTAL!$B$2:$B$484,CONNECTION_RECORDS_TOTAL!$B$2:$B$484,0),MATCH(CONNECTION_RECORDS_TOTAL!$B$2:$B$484,CONNECTION_RECORDS_TOTAL!$B$2:$B$484,0))>0,1))
 
Hi ,

This is a typical compute-intensive array formula ; you can reduce the computational load by using helper columns.

The example formula you have posted is just returning the number of unique items in the range B2:B484 ; of course this version of the formula is needed only if your range can contain text items.

If your range will contain only numeric items , then you can simplify it to :

=SUM(--(FREQUENCY(CONNECTION_RECORDS_TOTAL!$B$2:$B$484,CONNECTION_RECORDS_TOTAL!$B$2:$B$484)>0))

This will also be an array formula , to be entered using CTRL SHIFT ENTER.

Narayan
 
I use this formula for multiple different customers reports. Some have just text, others have numbers so rather than have different formulas for everyone, I use the same for all
 
 
You must log in or register to reply here.
Back
Top