Use Macro to replace =sum() for large of data

Hi,


This is what I currently have <Please refer to the sample excel document to get a better idea>:

Google Drive:
https://docs.google.com/file/d/0B89c...it?usp=sharing


I currently have =sum() formula in the STARTING from the column range:
EA101 --> =SUM(CA101:CA105) <<sum of 5 rows>>
Drag to autofill across, and to bottom, 4 rows above the data source (DA101:DW421). Otherwise, it will sum blank cells.

GA101 --> =SUM(CA101:CA106) <<sum of 6 rows>>
Drag to autofill across, and to bottom, 5 rows above the data source (DA101:DW421). Otherwise, it will sum blank cells.

iA101 --> =SUM(CA101:CA110) <<sum of 10 rows>>
Drag to autofill across, and to bottom, 9 rows above the data source (DA101:DW421). Otherwise, it will sum blank cells.

KA101 --> =SUM(CA101:CA115) <<sum of 15 rows>>
Drag to autofill across, and to bottom, 14 rows above the data source (DA101:DW421). Otherwise, it will sum blank cells.

MA101 --> =SUM(CA101:CA120) <<sum of 20 rows>>
Drag to autofill across, and to bottom, 19 rows above the data source (DA101:DW421). Otherwise, it will sum blank cells.



The Sample excel I have uploaded only consists of 321 rows (CA101:DW421).
However, the actual data (multiple sets of data) that I have consists of 300000+ rows.


As a result, the =sum() function that I have takes too long to caculate/out of memory everytime I provide new data from CA101:DW.


I am wondering if there is a faster way, maybe a macro, that could do the calculation instead of using the formlua?



Thanks,

Sheldon

Hi FDibbins,

Thanks for the reply & the advise =)

I actually tried when I posted, and I tried again following your instruction again just now.
Unfortunately, it shows that my file size is too large to be attached? (It is 3.8MB, what is the size restriction for attachment here?).
Maybe it is due to the large number of =sum() formula that make the filesize too large to be attached?

I will try to make necessary change again if required. After all, the last thing I want to do is to break any rules/policy here.

Thanks again

Originally Posted by FDibbins

Hi and welcome to the forum

Not all members can access file-hosting sites like google docs (company fire-walls and such). Please upload your file to the forum...
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.