[roasthawg]

So I've been doing that vast majority of my calculations in excel for a long time. I keep adding more and more stuff to my db's and I'm at the point where excel can't run my calculations anymore... it shoots me an error saying "not enough resources to complete this task." Very frustrating as I feel I have the data and the correct way to use it to improve my win percentage... but I can't crunch the numbers!

So basically I'm looking for advice on what to use... I scrape stuff to a mysql database but that's about it. I have never used Access much. I hate taking on the chore of learning something new when I'm so dialed in excel but apparently I'm going to have to. Which route should I go? What are others using that have too much data for excel? Excel is really easy to use to perform calculations in and my statistics programs run as an "add-in"... anything else out there as easy to use?

[Data]

Fine programming is the thing of the past. The current day solution to any IT problem is to "throw more hardware at it". You need at least a RAM upgrade, may be a new computer. For a reference, I have a spreadsheet that is almost 800MB in size, Excel takes up to 1.5GB of RAM to work with it but it is running just fine.

[RickySteve]

Does it still give you the error if you restart?

[TomG]

I agree with Data. Max out your computer's RAM. Consider upgrading to a multi-core CPU. With the right hardware, the latest version of Excel can handle some monster spreadsheets.

If that doesn't help, look into streamlining formulas/macros. Learning a new platform should be a last resort.

[uva3021]

Stata is easy to learn and far quicker

[roasthawg]

Fine programming is the thing of the past. The current day solution to any IT problem is to "throw more hardware at it". You need at least a RAM upgrade, may be a new computer. For a reference, I have a spreadsheet that is almost 800MB in size, Excel takes up to 1.5GB of RAM to work with it but it is running just fine.

Ah, a much easier solution than learning something new! All the excel forums recommend using a database management system rather than excel for large files. I've fought that idea tooth and nail by using every trick in the "excel book" to get what I need from my excel spreadsheets. A new computer is long overdue anyways (had my xps for almost 5 years now). Besides spending money I hate making the switch to a new computer... I setup computers at work quite often and it's just a chore to do in general.

Thanks for the responses, two votes for "upgraded computer" is enough for me as I would hate to stray from excel at this point.

[Wrecktangle]

Upgrading the computer is easiest, next is chopping the Excel sheet into cascading sheets, i.e. transforming your problem into one that can be done in chunks with each intermediate result imported into the next sheet and so on.

Another thing to think about is to "flatten" all your calculation into values vice calced formulas, for example for a set of long columns of calculated data sets, only have the first row with the calculations, "pound" (using a Lotus 123 term) out the formulas in rows 2 thru the rest of the sheet into values as formulas are much heavier than values. You keep the first row so you can upgrade formulas over time.

MSOffice 2007 & 2010 uses 64bit words so your sheets wind up getting smaller also (in case you are still using MS Office 2003 or earlier). Upgrading to to Windows 7 & the 64 bit world most likely will involve a new computer also.

[Indecent]

Might not be the advice you were looking for... If you are putting the data into a MySQL database as well, why couldn't you use a scripting language (php, python, etc) to handle the logic you need?

[Emily_Haines]

I have had success with just using the formulas for the last few rows so you have them for future entries. Take all the rest of the data and copy and paste back as text.

[Data]

MSOffice 2007 & 2010 uses 64bit words so your sheets wind up getting smaller also (in case you are still using MS Office 2003 or earlier).

Excel 2003 is perfectly capable of using 64-bit length words (values). The capability of using large 64-bit values has nothing to do with 64-bit system (processor + OS) support that provides an opportunity to work with larger (2GB+) files and more RAM. That 64-bit support was first introduced in Excel 2010, there is no 64-bit edition of Excel 2007. So, while 2007 and 2010 version share no unique property from any 64-bit related point of view, there is something that they have in common in different area. Starting with MS Office 2007, MS Office files are smaller in size due to MS Office applications using pkzip compression on the fly while saving a file. In other words, the newer MS Office files are essentially zip archives.

[roasthawg]

First thought for a new laptop... feedback appreciated.

Dell Inspiron 17R (had good luck with my current Dell)
6 gb ram
640 gb harddrive
Core tmI5 processor

It's only like $1000 which isn't bad at all. Is this ideal for handling huge excel sheets?

[roasthawg]

Might not be the advice you were looking for... If you are putting the data into a MySQL database as well, why couldn't you use a scripting language (php, python, etc) to handle the logic you need?

My scripting skills are basically limited to scraping websites at the present time. I have excel down pat... basically don't want to learn a bunch of new stuff if I don't have to.

[roasthawg]

I have had success with just using the formulas for the last few rows so you have them for future entries. Take all the rest of the data and copy and paste back as text.

Won't let me do this... "not enough resources" to paste special.

[Wrecktangle]

Excel 2003 is perfectly capable of using 64-bit length words (values). The capability of using large 64-bit values has nothing to do with 64-bit system (processor + OS) support that provides an opportunity to work with larger (2GB+) files and more RAM. That 64-bit support was first introduced in Excel 2010, there is no 64-bit edition of Excel 2007. So, while 2007 and 2010 version share no unique property from any 64-bit related point of view, there is something that they have in common in different area. Starting with MS Office 2007, MS Office files are smaller in size due to MS Office applications using pkzip compression on the fly while saving a file. In other words, the newer MS Office files are essentially zip archives.

This could be. However, when I asked my MicroSoft "expert" at work, he attributed it to operating under Vista 64. The files I had under Excel 2003 (32 bit) was more than twice as large as under Vista 64 with Excel 2007. Having seen it and verified by an "expert" I just accepted it and didn't take the additional step of verifying with MicroSoft.

[Wrecktangle]

Won't let me do this... "not enough resources" to paste special.

Sounds like you are right at the limit. Make a copy, strip out everything else but what you are flattening, flatten, save, open original, and paste from flattened file over original could work after you reboot and take care to do nothing else but this operation.

Otherwise, flatten by small chunks of rows (say, 100 rows at a time), and slowly work thru the entire data set.

[Data]

First thought for a new laptop... feedback appreciated.

Dell Inspiron 17R

Dell is a crapmaker and a bait-and-switcher. If I can ever give a good advice than perhaps the best one I can give is to always use http://www.resellerratings.com before you buy something online. Preferably, buy from vendors rated 9 or higher.

(had good luck with my current Dell)

Too small of a sample size, don't you think? http://www.resellerratings.com/store/Dell

Is this ideal for handling huge excel sheets?

While buying a computer online you have to consider three things in this order:
1) vendor
2) maker
3) specs

1) http://www.newegg.com/, always start here. http://www.resellerratings.com/store/Newegg

2) + HP, Samsung, Sony, Lenovo, Asus, Fujitsu
- Acer, Dell, Toshiba, Gateway, MSI

3) If you are looking for "ideal" or close you must go with i7. For the same money, a desktop will be faster than a laptop. Here is an example:
http://www.newegg.com/Product/Produc...82E16883147373
Here is a good albeit a slower laptop for the same money:
http://www.newegg.com/Product/Produc...82E16834110415

[roasthawg]

Sounds like you are right at the limit. Make a copy, strip out everything else but what you are flattening, flatten, save, open original, and paste from flattened file over original could work after you reboot and take care to do nothing else but this operation.

Otherwise, flatten by small chunks of rows (say, 100 rows at a time), and slowly work thru the entire data set.

100 rows at a time would take for ever with over 250,000 in the set.

[roasthawg]

Dell is a crapmaker and a bait-and-switcher. If I can ever give a good advice than perhaps the best one I can give is to always use http://www.resellerratings.com before you buy something online. Preferably, buy from vendors rated 9 or higher.



Too small of a sample size, don't you think? http://www.resellerratings.com/store/Dell




While buying a computer online you have to consider three things in this order:
1) vendor
2) maker
3) specs

1) http://www.newegg.com/, always start here. http://www.resellerratings.com/store/Newegg

2) + HP, Samsung, Sony, Lenovo, Asus, Fujitsu
- Acer, Dell, Toshiba, Gateway, MSI

3) If you are looking for "ideal" or close you must go with i7. For the same money, a desktop will be faster than a laptop. Here is an example:
http://www.newegg.com/Product/Produc...82E16883147373
Here is a good albeit a slower laptop for the same money:
http://www.newegg.com/Product/Produc...82E16834110415

Invaluable information my man... many thanks!! Needs to be a laptop as I am in between work and home a bunch. Do you have a specific vendor and make in mind that would accomplish what I'm looking for? Thanks data.

[Data]

I asked my MicroSoft "expert" at work

90% of IT professionals have very little clue about what they are doing. Still, by leaps and bounds, they know more than non-IT computer "experts" who, by leaps and bounds, know more than an average user.

My advise to everyone, do not listen to your IT guy, those clowns are full of shit.

[Data]

Do you have a specific vendor and make in mind that would accomplish what I'm looking for? Thanks data.

Use the link on the bottom unless you are willing to spend more. In that case, what is your budget?

[roasthawg]

Use the link on the bottom unless you are willing to spend more. In that case, what is your budget?

That price is actually right about what I was hoping to spend although I could go higher if the increase in performance is big.

[Dex17]

I have had success with just using the formulas for the last few rows so you have them for future entries. Take all the rest of the data and copy and paste back as text.

You'd probably want to paste special as values rather than text.

Many have mentioned this type of tactic - probably the easiest; I can't imagine you would need to upgrade your hardware. It seems like you don't need all of your embedded formulas for data that you're just pulling.

Like someone else mentioned, just streamline your model and/or build in some macros. It's got to be built to scale!

Good Luck.

[hubie69]

The honest best way to do this, go get a cheap low end P4 box with 2 GB of ram, throw a command line version of linux on it along with MySQL. The Linux will use no resources at all, mysql wont really either, then you can parse that data in a variety of ways, php, shell scripting, you name it. I do this and I have it run on a P3 1.5/2GB RAM

[mminkovski]

no need to buy a new PC just because your file is too big. You can save to a txt and work with any DB analysis software. There are quite a few good and free options. It would take more time to get used to it but you'll never run into resources issue