Excel 2007 : Excel 2007 - Calculation time

[carlo1951]

File created in excel 2003 than moved to excel 2007
Main functions From an alarm list to define alarm type and affected locality/equipment
(used formulas (e.g) =SUMIF('#A'!$F$2:$F$13000,"*"&'#S'!AD$5&"*"&'#S'!$E15673&"",'#A'!$B$2:$B$13000)-SUMIF('#A'!$F$2:$F$13000,"*"&'#S'!AD$5&"*"&'#S'!$E15673&" ""*"&$A$1&"*",'#A'!$B$2:$B$13000)-SUMIF('#A'!$F$2:$F$13000,"*"&'#S'!AD$5&"*"&'#S'!$E15673&" ""*"&$A$2&"*",'#A'!$B$2:$B$13000) where #A and #S worksheets.

Previous: Excel2007+WindowsXP+2 processor= OK calculation time acceptable

Now: Excel2007+Windows7+4 processor +64 bit = Loooong calculation time (up to 5-6-times)

Attempts: Windows7 compatibility with XP, range.calculate, etc NO result
Last: Calculation in Macro without involving the worksheets removing risks related to links, re calculations etc: NO results LOOOOOOOOOOOng calculation times.
Has anyone had this tipe of issue?, any suggestion about olution?
Thanks
carlo1951

[MarvinP]

Hi carlo 1951 and welcome to the forum,

DonkeyOte has two links in his signature line that may explain part of the problem:
http://www.decisionmodels.com/calcsecretsi.htm and
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

If you are using VBA in this workbook you can turn calculation off while you enter new data using
Applicaton.Calculation=xlManual
and then calculate only when needed.

You can also calculate a small range of a workbook using Range(A1:B10).calculate.

also read http://msdn.microsoft.com/en-us/library/aa730921.aspx

I hope this helps.
BTW - the .xlsx file size is much smaller than .xls but I don't think the calculation times were much slower.

[carlo1951]

Hi marvinp,
thanks for the tips but:
1- the file is normally run by a VBA where the Calculation is set in manual.
2- The calculation set by range.calculate
3- after other unseccessful attempts (looong calculation time):
a- Calculation performed in the VBA filling a matrix
b- removing all other formulae & worksheets and manually operating (copy the SUMIF and calculate) I arrived to the conclusion that the calculation of SUMIF formulae is the (secondary) cause of the slow down.
The primary cause is still not explained (why with XP everything was running faster?)
PS the file size (.xlsx) is 5Mb when running approx 8Mb

[MarvinP]

Hi Carlo,

I've had programs take a few days to calculate but that was helped by improving my VBA. I do remember reading that Excel 2010 improved many calculation times - read http://msdn.microsoft.com/en-us/library/ff700515.aspx
In http://support.microsoft.com/kb/825012 it explains older versions of Excel and calculation methods. I don't know if these apply to your problem.
In reading more articles it seems multi-threading of processors was added to Excel 2007 so there were some changes in calculation methods, see http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
I'm sure you don't have memory swapping or disk swapping events, do you?

Do you have the latest Software Patches from Microsoft? SP's? See another thread at http://www.eggheadcafe.com/software/...-and-2007.aspx

I hope all this helps you figure it out.

[carlo1951]

Hi Marvin,
This are my conclusion:
- The cause of the looong calculation time are the formulae SUMIF withh the character * (e.g.SUMIF('#A'!$F$2:$F$13000,""&'#S'!$E16065&"*"&'#S'!Q$5&"*",'#A'!$B$2:$B$13000)-SUM......................
I can not isolate during calculation because THIS are the one to be calculated.
The Number of these formulae (activated by a VBA) are 19833
I assume that these SUMIF (with *) are "volatile" and consequently ricalculated.......

The Way out that solved my problem (I do not cosider it a solution):
Apply formulae line of the alarm list looking at each alarm type e locality (e.g IF(AND(Z$1=0,$B2=0),0,IF(ISNUMBER(FIND(Z$1,$F2)),TRIM(SUBSTITUTE(REPLACE(LEFT($F2,FIND(Z$1,$F2)-2),1,FIND(" ",$F2,FIND(Z$1,$F2)-9),REPT("",FIND(Z$1,$F2)-2-(FIND(" ",$F2,FIND(Z$1,$F2)-9)))),"CMC",REPT("",3))),0)).).
Total 1975848 formulae
Hoping this experience can help someone else.....