The question I have is to simply understand if I am doing something wrong with my workbook or it is normal for Excel to take this long to calculate.
I have a workbook of 4 sheets,
Sheet-1 has numerical values in 5000 rows and 8 columns (no formulas)
Sheet-2 has links to sheet-3 and 4. It has 5000 rows and 77 columns with formulas that are just links to Sheet3 and 4, Rows and Columns.
Sheet-3 and 4 have also 5000 rows and 77 columns. Formulas are the same in both sheets but each sheet outputs different results based on criteria’s entered in a single cell. Most of the formulas are multiple (IF, SUM, RUNDOWN, INDEX MATCH, AVERAGEIF) and links to import data from Sheet-1. These statements are present in various combinations and in various columns. Each Row formula however is exactly as the Row above.
In Sheet-2 A VBA timer code automatically writes a value of (1) in Cell A12 then if rows in Sheet-3 and 4 see the value (1) it then calculates the respective rows and its results appears on Sheet-2 row, then converts it to value and moves on to row 2 and repeats itself.
File size is 16MB
My pc Has 12GB ram
8 Processors Intel Core i7 975 3.33GHz Intel
Why does it take over 15 hours to calculate all the rows? I understand that every time there is an input the whole workbook has to recalculate. But 15 hours? How does anyone process large amount of data?
I have tried codes where the rows would copy and paste row by Row after calculations. And that took twice as long to calculate. (I think it performed something like: calculate, then copy and paste, recalculate, then convert to value, then recalculate. Then copy and paste.)
I have tried codes to convert each calculated Row on each sheet to value and that also took nearly 30 hours. Basically recalculating the workbook multiple times before moving to next row. Painful.
The attached code however has been the fastest just by simply leaving all formulas of sheet 2 and 3 in place and converting results to values just on sheet-1. (I think it calculates 2 times before moving on to next row)
The pain is that every time I have to change a criteria I have to wait over 15 hours to get all the results. (
I have been looking for solutions with no success. The codes I have used were provided to me by helpful people on this board, And I am grateful to them as the codes work perfectly on small data workbooks.
Is there something I can do to speed up the calculations? Or I should just accept the fact that it will take 15 hours and stop looking for faster codes?
Thank you for your info and input on this matter
All the best
Nino
Please Login or Register to view this content.
Please Login or Register to view this content.
Bookmarks