Hi am building file for our company to analyze the amount our clients customers spend over 3 months, all within certain distances of their store. The data set I use as a baseline (client ID, radius from store, trips per month and avg. spend per month) are copied and pasted into 12 columns of an excel file (with up to 300,000 rows/records, which feeds into formulas I have written to analyze this info on various levels. Ultimately all the data flows into a predictive pro forma we created that is unique to the work we do. The worksheet has grown as our interest in looking at the data from different angles has grown. The problem is the size of the file is now so large it take a great deal of time to open, and worse, to recalculate each time I either cut or copy and paste data from one area to another. I am hoping to find ways to shrink the file from its current 80MB size so wanted to turn to Excel Forum for advice/tips.
A few things to note:
I have noticed the system crashes about 3 times a day for no apparent reason. I assume the calculations freezes the computer brain. I am using Windows 2011 for Mac.
I am not using many Named ranges, which perhaps I should do if it helps streamline the process.
When I copy and past the initial set of data, which could include up to 300,000 rows (12 columns), I have formulas that run down parallel columns performing calculations of the data. Most of the data only covers 100,000 records, but I have the formulas run up to the allotted 300,000 rows to make sure I do not paste outside of the last row, thus messing up my calculations.
I used to have the analysis broken up into several different worksheets, but decided to combine them to minimize the chance for broken links as well as to more easily manage updates or bug fixes. The file is ultimately sent to the sales reps who use the results (pro forma tab) in their presentations.
Thanks in advance.
Bookmarks