Speed up spreadsheet calculations

[mcinnes01]

Ok so I have got a spread sheet I have been working on actually working now, my only problem is that the sheet is really really slow. It isn't too bad on my pc, but on the peoples machines that this will be run on they have excel2003 where as I have 2010.

It currently takes about 5min to fully load on 2003 where as it takes about 30secs on 2010. (I know 2010 can perform 1024 calcs at once and 2003 can only perform 1) however I need it to work well on 2003

Does anyone know how I could make this work but a lot quicker?

Hope you can help it is death defyingly slow at the moment!

Cheers Andy

[Richard Buttrey]

Hi Andy,

The first thing I noticed was that the last used range on some of the sheets is way down the sheet, particularly on the sick record sheet. Do and End Home on here and you'll see you end up at row 30000 + with formulae all the way down column U.

Tidy up the workbook by deleting all unused rows and then resave to make sure Excel clears its memory of these cells.

In addition since you're asking people who only have Excel 2003 to use the WB, then there's no point in saving it as an .xlsm workbook. You should be saving it as a .xls workbook. Better still, and since you should always design for the lowest common denominator, I'd advise you to develop it yourself in 2003, that way you'll avoid the complications of using the new 2007 functionality accidentally.

HTH

[squiggler47]

Excel 2003 is comparable with 2010 in speed, are you saving it as an XLS or an XLSM file?

If you are saving it as an XLSM file and reading it it may be the translation that takes the time reading!

Excel 2010 is indeed threaded but will only calculate per processor so it will only calulate 2 formulas on a dual core and 4 on a quad core, trying to calculate more on 1 core would result in the sheet calculating slower rather than faster!

There are few formulas in your sheet so calculations shouldnt be a problem, and your macros seem small so you should be ok!

you may want to change your worksheet change event

Please Login or Register  to view this content.

This prevents worksheet change triggering when it updates cells its self!

It might make your sheet quicker!

[squiggler47]

as a side note you might like this for easter sunday :-

=FLOOR(DATE(YearSelected,5,DAY(MINUTE(YearSelected/38)/2+56)),7)-34

I forget where I got it perhaps the Spreadsheet Page!

[DonkeyOte]

Further to all of the above regards the calcs themselves...


Without getting too involved in testing etc

Please Login or Register  to view this content.

with each Defined Name referencing 65k rows each SUMPRODUCT is referencing 180k rows.
180k * n SUMPRODUCTs is going to have a pretty dire impact on performance

To improve performance (pre XL2007) add a concatenation column to SICKRECORD that combines Employee & Absence Type, eg:

Please Login or Register  to view this content.

Assume the above is named SICKKEY then with the concatenation column in place you can remove the SUMPRODUCT and work with SUMIF which will be significantly (significantly) faster from a calculation perspective

Please Login or Register  to view this content.

[mcinnes01]

Thanks for all the replies I am just going through making changes now to see what I can do, I will post back when I am done so you can see if there is any improvements I missed.

Donkey Ote I have a possible silly question, but with the concatenate, how does this work exactly?
Thanks

Andy

[DonkeyOte]

with the concatenate, how does this work exactly

The concatenation simply creates a single "field" containing all values of interest.
Here we combine "Ee" with "Sick Type" giving us "Ee@SickType" value on each row in our data set.

The concatenation key means that in effect the "multi criteria field" calculation can be dispensed with as we can use the single concatenation field with SUMIF or COUNTIF etc... (and use wildcards as necessary)

These things are always best illustrated by examples - below is a demo. of this approach c/o Mike Girvin

http://www.youtube.com/watch?v=ry3vSqN1TQ0

[mcinnes01]

Hi squiggler,

Thanks for the help, I am just putting the change event in now. With regards to the easeter calculation, a beast as it is, it uses a very complex calculation that predicts easter as far in to the future as can be imagined. With out going too far in to it easter calcs are based on lunisolar cycles. The set of bank holiday calcs I have collected I feel accurately calculate the dates please feel free to use them if you like!

[DonkeyOte]

For those interested re: the date calculations.... on behalf of mcinnes01 see prior threads on that topic:

http://www.excelforum.com/excel-2007...day-falls.html
http://www.mrexcel.com/forum/showthread.php?t=473654

[mcinnes01]

Hi Squiggler,

Just altered the change event as you suggested, but it is not populating the output details now, any ideas where I'm going wrong?

[mcinnes01]

Thanks donkeyote, I also understand the helper column it works almost as an index for the particular info that needs refering to. Simple and effective! thanks

[mcinnes01]

Hi Squiggler,

Got it working there was a space between enable and events.

It works great thank you!

[mcinnes01]

My sheet is working a lot quicker now! Take a look for yourself if you like...

For the purpose of this forum I have had to keep the current file format as .xls makes the file 7.6mb, but you can save it as 97-2003 and see how much quicker it loads.

Thanks for the help

Andy

[squiggler47]

Hi squiggler,

Thanks for the help, I am just putting the change event in now. With regards to the easeter calculation, a beast as it is, it uses a very complex calculation that predicts easter as far in to the future as can be imagined. With out going too far in to it easter calcs are based on lunisolar cycles. The set of bank holiday calcs I have collected I feel accurately calculate the dates please feel free to use them if you like!

The calculation given is accurate up to 2099, this one is accurate for all years within the excel date system,it was written by me from the gaussian formula :-

Please Login or Register  to view this content.

[mcinnes01]

Thanks Squiggler I will test it out!