Why would "identical" spreadsheet run at different speeds?

[mrblister]

I'm working on a large workbook. And each time I save, I save under a different file name (I increment the book number).

I noticed that the spreadsheet was running slow (let's call the latest version book A). I opened up older versions of the workbook, and discovered that the slowdown happens 5 builds (saves) back (let's say book E is the last "speedy" save). I have a macro in both the workbooks that tell me how fast calculations run, and Book E is about twice as fast as Book A (~1.5 seconds vs. ~3 seconds). These speeds are consistent. So something happened between book E and book D that causes the slowdown. I couldn't find the issue. So what I did, was I copied over changes in formulas from book A and pasted them into book E (we'll call it Book A-E). Functionally, and as far as I can tell, Book A and book A-E are identical now. BUT, Book A-E runs nearly twice as fast as Book A.

Now it's possible I missed transcribing something from book A to book E (that causes the speed drop), but it's unlikely because within each workbook the data in the tabs are dependent on each other, and they both are giving matching solutions. So let's assume both spreadsheets have matching formulas. What could possibly be causing the slowdown in book A?

Notes:

• I had book A and book E open in different instances of Excel, and both were open like this during the speed tests.
• Running Excel 2010, 32-bit.

 

[mole999]

Would be good to see some of the complex formulas, and any that are volitile

 

[mrblister]

This issue has me quite frustrated

Because it doesn't make any sense! I'm trying to speed up my spreadsheet, and it just does the opposite. Anything I do seems to slow down calculations.

Would be good to see some of the complex formulas, and any that are volitile

So the formula that fills the sheet, was originally (and I guess still is):

=IF($A5>0,IF(MOD($A5,VLOOKUP(C$18,SomeTable,[B]MATCH(VariableA,TableHeaders,0)[/B],0))=0,VLOOKUP(C$18,SomeTable,[B]MATCH(VariableB,TableHeaders,0)[/B],0),0)*IF(C$12=1,1,O2),0)

That formula is filled down and across. I noticed that the sections in bold repeat in every single formula, so I removed them:

=IF($A5>0,IF(MOD($A5,[B]VLOOKUP(C$18,SomeTable,$A$1,0)[/B])=0,[B]VLOOKUP(C$18,SomeTable,$A$2,0)[/B],0)*IF(C$12=1,1,O2),0)

Then I noticed that the Vlookups are identical for all the formulas in the column, so I moved them out to the top of the column:

=IF($A5>0,IF(MOD($A5,[B]A$3[/B])=0,[B]A$4[/B],0)*IF(C$12=1,1,O2),0)

I updated all the formulas with the above. Then I saved it. Let's call it Book B. Before the update above, I started from Book AE-2.

So book B runs SLOWER than Book AE-2 (I can't remember how much, but it was significant)! And somehow Book B is now 50% larger in file size! Book B is 60MB, compared to 40MB for Book A-E2, and takes far more memory!

My guess at this point is that the worksheet I was working on (or maybe the entire workbook) is corrupted. I converted Book B to an XML file to see if I could see the issue, but the worksheet is over 300MB (when uncompressed from the XLSX) and I can't open it because I don't have nearly enough ram to open a file that large (It took up over 8GB memory during loading, before I panic-quit).

OK, so working on the assumption the sheet is corrupted, starting from Book A-E2, I created a new tab (which takes a loooong time. In fact, just moving cells also takes almost 30 seconds). Then I copied and pasted the formulas (paste-only-formulas) from the old tab to the new tab. Then I changed all the named ranges to point at the new tab, then deleted the old tab. We'll call this Book C. Book C... runs about 20% slower than Book A-E2. It's consistent, after saving and reloading. But it doesn't make any sense! They are the EXACT same formulas (the sheets should be identical)! I even tried the Control-tab trick on both Book B and Book C (and Book A-E2) and the last-cell looks fine.

BUT... then I noticed that Book C is now 30MB (was previously 40MB from Book A-E2). I can't explain the size reduction. I also can't explain that even though it has the same formulas, and is now 25% smaller in file size, it runs SLOWER than the "identical" Book A-E2.

I also tried the "repair" option in Excel, but it doesn't affect the file size or the speed.

So... WHAT is going on?!? Does anybody have any ideas (or solutions)?

 

[mole999]

If you do get it open save as xlsb. 300Mb is big (but in 8Gb it should open), but providing you have hard disk space then it should virtualise what it needs, make sure you are not running anything else while you are doing this, give it as many free threads as possible