Veeery Slow calculation - file 5 to 10MB, a lot of formulas

[igeron]

Dear Experts,

I built an excel template, to make complex analyses. The template file takes around 4-5mb when empty and close to 10 MB when filled.

I used a lot of If, sumif, countif, sumproduct, offset, names, macros and graphs. Some of the typical formulas;

=IF(D78=0,"",(SUMPRODUCT((OFFSET('Sheet1'!$A$41,,,EEN)=$C$2)*(OFFSET('Sheet1'!$H$41,,,EEN)<14),(OFFSET('Sheet1'!$Z$41,,,EEN))))/$D78)

or

=SUMIF((OFFSET('Sheet1'!$P$41,,,EEN)),$C$2&$C84,OFFSET('Sheet1'!$AG$41,,,EEN))

I had some UDFs, but I removed them to make it faster. Still, even hiding a row takes 2-3 seconds.
Please help, how I can make it faster.

Thanks
Igal

 

[olympiac]

If you have got Excel 2007, you should try saving your file as "excel binary workbook".

 

[igeron]

Hi Olympiac,

I am using and the final users will have Excel 2003.

Thanks
Igal

 

[RoryA]

Try and avoid OFFSET as it is volatile and will recalculate whenever anything in the workbook does. INDEX is usually a better option.

 

[igeron]

Hi Rorya,

thanks for the tip, index helped much, but still I have some way to go
do you think I may have other volitiles or is there a way to spot volitile functions within the file?

I am using a lot of sumproduct and suspect that they may slow the file as well.

 

[RoryA]

See this page (and that site in general) for calculation tips. If you have a lot of SUMPRODUCT, look at using key columns and SUMIF, or pivot tables instead.