Calculating workbook - Over 15 hours?

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.

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thank you John for your reply,
I have attached a sample workbook With the VBA Code as you requested. Inside I wrote a message for you with more details hoping to clarify its purpose.
You may discover that some formulas can be simplified, but its the best I could do based on my Poor Excel knowledge
Cheers
NIno

I ran the computation with the timer set to 00 it only reduced the computation by about 1 hour. But the problem running without the timer is that I am not able to stop the computation at any time I choose to, and if I try using the ESC option to alt the computation Excel freezes and forces me to restart.

Hi, I am not too familiar with the codes as they have been provided to me by the helpful members in this forum. What exactly do I need to eliminate? Are you asking me to remove the whole code in the timer sheet? And if so how would I be able to convert the row results into values?
Thank you

Thank you for your help Xladept

Your code has reduced calculations per row by nearly 2.5 seconds and I am sure as the calculations advance the timing will reduce even more
Also I was getting a debug window when I first tried your code. I removed (Next) and it all worked fine (not sure why)
Thank you very much for your help
much appreciated
Cheers
Nino

Hi Xladept,
I am going to run the whole workbook in a bit, I will know exactly how long it will take to complete (by tomorrow I assume).
And I'll be sure to inform you about your contributed code improvement.

Cheers
Nino

Well xladept,
your code ultimately shaved an average of 2 seconds per row
from 13 sec to 11 sec.
However, today I tried something different
I separated the workbook-sheets in separate workbooks, each with its own data.
I ran separate instances of excel and ran both workbooks at the same time.
Although nothing changed in the formulas and codes, this time it took just under 7 seconds to calculate both workbooks.
I don't get it. You would think that if the sheets were in same workbook it would be faster. But, Nope.
Anyway, I am just Glad that I am able to see the results in 7-8 hours rather then 15-18 hours

I thought I'd share this discovery with you.

Thank you once more for all your help
Cheers
Nino

since it have lot of formula, will Turn Off Automatic Calculations is work in this situation?

turn off before run the code
and
turn on after code run

Step to set manual calculation
Formula (Ribbon) > Calculation (Section) > Calculation Options > Manual

Ok,
Imagine this scenario:
There is 10 years of data. from 2005 to 2015
If I would start trading on day one of year 2005 then year end of 2005, 2006, 2007, and so forth would give me results of gain and loss for the respective years. Those results would need to be converted to values so I can view the different results based on what day the trading begun.

This time having started on day two of year 2005 rather than day one the results of the respective year end will be different.
And the same for every day forward. For each day eliminated from the beginning of 2005 the respective year end will produce different results. With that, I am able to see if at any point in time there would be a failed or negative result.

What the timer does is:

By placing a Value (1) on timer sheet the respective row gathers the year end results from the other sheets and converts them into values on timer sheet.
The other two sheets whenever they see value (1) from timer sheet the respective formula eliminates the row data. Hence, producing a row of Zero (as if that day never existed) then all the calculations would start over again. Without counting the eliminated data.

Hope that made sense

But naturally if you think there are better ways to do it I would gladly implement and test the better ways

Cheers
Nino

Hi BoredWorker,
I tied that, But it didn't work.
It still took same length of time to calculate.

But thank you for the suggestion
Cheers

xladept, I think I misunderstood your question.

The reason I am using the timer is because i can't stop Excel from calculating without having to resort to the ESC key. The (1) second pause allows me to press the stop Button when I need to pause the calcs. (Often)

I wish i could remove the timer and still be able to click on a button to pause it. It would obviously save me a sec per row.

Originally Posted by Daishiknyte

See if this works for you. Check the columns copied to make sure I got the correct ones. Only took ~60 seconds to run. Your use of the worksheet_change() event was killing you. Remove it and your timer code.

Please Login or Register  to view this content.

Hi Daishiknyte.
Thank you for your response and code.
I am sure I am doing something wrong applying your code. I am not getting the results in the columns.

I have removed everything from the Module and the sheet and pasted your code in the module.
If that's incorrect could you please give me what steps I need to take?
Or maybe you could place your code in the (Sample Workbook ONP) previously uploaded and then upload your modified (Sample Workbook ONP)
I can then view it and test it
All I was able to see with your code was correct rows and columns convert to value (but no results in the cells) and correct Placement of value (1) in column A
Thank you so much for you help
Cheers
Nino

I goofed. The code I posted does not give your sheet a chance to do its work before copying over. Double check my understanding and I'll take another stab at it:

From what I saw, it looked like each line was being set with a "1" in column A before "fixing" the values in place by copying the value over the equation in the cell. Is there more going on than that?

Yes. You understood correctly.
When "1" appears in column A cell A12, row 12 formula results convert to Value. Then it moves to next row A13 and same occurs to respective row. And this procedure will continue down to row 5000
Thank you for trying Daishiknyte, Much appreciated
Cheers

Alright. Try, try, try again. In case there's something about your sheet that needs each line fixed as you go, here's one with the calculation run each step. If it's ok to wait to run the calculations until the end, the do-while and the copy-paste can be moved outside of the for loop.

Please Login or Register  to view this content.

ARE YOU KIDDING ME????
YOUR CODE HAS PROCESSED ALL CALCS IN 01:28:20.81

WOW, WOW , WOW))
You are a genius
Are you sure you are not working for NASA or SPACEX?
i mean...how can i possibly get results in just over one hour when before it was over 15 hours.
My hat goes off for you . JUSt Brilliant
Thank you so much Daishiknyte I will certainly rep. you
I will rerun the test and recheck it.
Just brilliant Mate (allow me to call you Mate)

and thank you for replying so rap
Just Brilliant
Cheers

Hi Bored Worker, thank you for your suggestion
I did try your suggestion as well, I ran both instances at different time and the calculation time ran about 2 tenths of a second longer per Row on average. Will have to retry the test with all applications off to get a better read.
Thanks again
Cheers

Daishiknyte, I wanted to thank you.
I am happy with the calculation speed that you have allowed me to accomplish.
If I do learn something new regarding this matter I will certainly pass it on
Meanwhile I'll keep running tests and checks for potential improvements

Thank you

Hi Daishiknyte, I was wondering if a separate code or Button can be implemented in your existing code that would allow me to stop the code from running.
As of now the only way I am able to stop the code is by pressing the reset button in the VBA window.
Cheers

None that I know of. Possibly hit the pause/break key on your keyboard. Your best bet is probably to look at reworking your sheet to reduce the time it takes for the page to calculate during each iteration.

Hi, I just wanted you to know that your modification to the code shaved 35 minutes from the copy paste code.
Thank you
NIno