StevenEdmonton
Board Regular
- Joined
- Aug 16, 2011
- Messages
- 76
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
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