loop vs. simple function, huge difference in speed, why?

[losmi8]

Hi guys. I have a problem with one of my loops, it takes about 17 seconds to do the job of calculating a simple moving average for 200 periods on 20,000 rows. However, if I do the "FillDown" function for the same type of average, it takes 1 second.

Here is the code for the loop:

Please Login or Register  to view this content.

And here is the other code that does the same job in 1 second:

Please Login or Register  to view this content.

I've used the following to calculate how long each takes:

Please Login or Register  to view this content.

That is where I get the 17 vs 1 second.

Why such a huge difference between the two?

PS. I did notice that if I run another loop right (or a procedure) after the second code (this new loop/procedure uses the 2nd codes moving average numbers from Column "O" obtained by the filldown function) after a few rows, the numbers don't make sense. Why is that? It's like the fill down function is still calculating EVEN after the procedure has ended.

If anyone can shed some light on this, I would really appreciate it!

[losmi8]

Another interesting observation, loops that perform much more complex calculations on the same data of 20,000 rows ... they take about 7 seconds.

What is so wrong with my first code?

[losmi8]

anyone? i still haven't found a solution to this..

[Paul]

You're wondering why performing a double loop with essentially 3,959,800 iterations takes longer than putting a formula in one cell and using the built-in filldown function to copy that formula down 20,000 rows at the same time??

Your outer loop, assuming you have 20,000 rows of data, is 19,799 loops [2 to LR (20000) - MAperiod (200)]. Your inner loop, which is completely run for each of the 19,799 outer loops, consists of 200 loops [1 to MAperiod]. Within each inner loop you're performing multiple addition/subtraction and variable updating - 200 times per 1 outer loop iteration. Then in each outer loop iteration your again taking variables and performing math and updating them. Can you see the trend here? You're doing millions upon millions of calculations in your loops. Even though worksheet calculation is turned off (thankfully!) the macro still needs to calculate all of those values every iteration.

Performing a complex calculation on 20,000 rows of data with just one pass through the range SHOULD take less time than your first macro. Unless it's truly complex.

My suggestion.. If you have two sets of code and one takes 1 second, the other 17, and they both have the same result, stick with the one that takes 1 second.

[losmi8]

Like I said, they do perform the same calculation...But after the FillDown, if I run another procedure or a loop, the numbers are off. It doesn't seem to be accurate. It is accurate for the first few rows, but it looks like after the filldown is initiated in a procedure and then another loop uses those values from filldown procedure, it seems like the loop runs faster than the filldown. Hard to explain, but filldown appears to be fast, yet it makes mistakes if something uses those numbers shortly after...Does anyone get what I am trying to say?

And Paul, the way I wrote that loop, is that okay? The 17 second run time, how does that sound? This is one of my first loops, so I have no idea how fast they should run.

[Paul]

A 4 million iteration loop in 17 seconds, probably not bad. Not good practice, but at least it's not taking 3 hours to complete.

The only difference I see between the loop and the filldown, is that the loop puts the actual average value into each cell in the column, while the filldown puts an average formula in each cell. In the filldown code, you could probably add three lines such that:

Please Login or Register  to view this content.

[losmi8]

Ooh! I think you got it! So it completes the task of putting the FORMULA in each cell in 1 seconds, and those formulas take some time to do their job, so that is why I am seeing a lag with that approach!

So what you are saying to do with those three additional lines is that I go ahead and copy the cells, and then paste just the values back in their position? And this will take care of the "lag" issue you think?

[Paul]

It might, give it a shot and report back.

[losmi8]

Paul, it does the exact same thing. I think you solved it though, the filldown is quicker because it finishes putting formulas in each cell, and it takes a while for those formulas to calculate each calculation (it would make sense for them to take ~17 seconds), correct?

[broro183]

hi Iosmi8,

Using a native functionality of Excel is almost/always (?) likely to be faster than using a loop due to the iterations as Paul has mentioned. The loop is further slowed by the fact that each loop has "overhead" caused by referencing a spreadsheet cell. This could be made faster by using a variant array, calculating in memory & only writing back to the sheet (in one go) after all calculations are complete.

However, on the basis of native functionality being faster...
Here's a slight (untested!) modification, as the Filldown isn't needed if you are only worried about the value (ie not formatting):

Please Login or Register  to view this content.

Do you have the correct relative & absolute references (ie dollar signs) in the formula string?

hth
Rob