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

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!

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.

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.

It might, give it a shot and report back.

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