Need a more efficient way to calculate Standard Deviation

[StevenEdmonton]

I have a spreadsheet that contains 200,000 rows and about 60 columns. In one section of the worksheet I have about 10 columns that calculate different standard deviations for a total of about 2 million calculations. This is making the memory usage of my computer very large, about 600,000K as stated by the task manager. I am trying to find a way to reduce the memory usage of this spreadsheet.

I need to find if there is a more efficient way to calculate standard deviation. For example, at the top of each column I input the amount I want to look back to calculate the standard deviation. I want to calculate a continuous range of ~100 values, I would simply enter 100, in cell F1 and fill down the following formula from F2, which I would fill down to the 200,000 row to calculate the standard deviation. All of my raw data is in column E.

F2:F200,000 = STDEV(E2:OFFSET(E2,($F$1-1)*-1,0)))

Can someone please let me know if there is a more efficient to do what I want so that I can reduce Excel's memory usage.

I have a sample file with 500 rows but I do not know how to post it. If someone could tell me how to do that then you can get a better idea of my problem.

Thanks, Steven
​

 

[shg]

First, you could use an INDEX function rather than OFFSET, which is volatile:

=STDEV(E2:INDEX(E:E, ROW() - $F$1 + 1))

Second, if that's still a resource pig, you could calculate an incremental deviation line by line. That would require some helper columns, but it would be a lot faster, I think.

 

[StevenEdmonton]

Hi shg,

I tested your suggestion on just 2 columns and found that my memory usage increased from 588,000K to 658,000K. I would like to try your other idea of helper columns but I am unsure of the best way to breakup the formula. Is it possible to give me some suggestions/examples.

Thanks, Steven

 

[shg]

... and found that my memory usage increased from 588,000K to 658,000K.

That's interesting; you could change the formula to

=STDEV(E2:INDEX(E:E, A2))

... if A2 contains a formula for the row index based on the aperture size (the formula would serve all columns, assuming they all have the same aperture). But my main thought (and apparently not yours) was not memory footprint but volatility, so you don't have all 2M cells recalculate when you change any value on any sheet.

I went back and looked at incremental SD; it requires four helper columns (for aperture sum, aperture average, value squared, and some of aperture squared). That certainly won't reduce memory footprint, but it avoids a lot of duplicate calculation for large apertures, so it's fast.

You'd get the smallest memory footprint with VBA; nothing gets stored but the numbers and a small amount of code. The code would probably be slower than formulas unless it used the incremental approach and the aperture was large.

You might try using relative named ranges. The formula in each cell would be something like =STDEV(aperture), where aperture is defined relative to the cell in which the formula appears and dependent on the aperture size.