Excel and RAM. OSX

Reply

  #1  
Old 06-06-16, 01:53 PM
Member
Thread Starter
Join Date: Oct 2014
Posts: 643
Received 0 Votes on 0 Posts
Excel and RAM. OSX

I'm trying to use excel for something it wasn't really designed to do and it keeps crashing on me. I know I should just use matlab but it's not THAT complex and I'd rather not have to deal with moving everything to a new program

I've broken the calculation up piecemeal and it seems to be working (I'm about 30% through) but still intermittently crashing. Watching the computer's activity moniter I can see the memory pressure increasing steadily during the calculations but excel always crashes before the memory pressure gets even to 50%. Is there any way to check and see if OS X is imposing some kind of limit on Excel's RAM access.
 
Sponsored Links
  #2  
Old 06-06-16, 06:07 PM
Member
Join Date: Jan 2008
Location: Southeastern Pennsylvania
Posts: 2,948
Received 0 Votes on 0 Posts
Isnít there a Console Log associated with OS X? Iím not familiar with OS X but there should be log entries of some type that can be viewed after the App crash that may be helpful.
 
  #3  
Old 06-07-16, 12:19 PM
Forum Topic Moderator
Join Date: Sep 2005
Location: USA
Posts: 4,713
Received 29 Votes on 28 Posts
Make sure you're using the current/most recent version of Excel (14.6.4). There are a handful of arcane issues that each release seems to fix. I'm not certain it will help, but it's definitely worth a try.

I don't know of any specific memory limit or memory management that Excel or Mac
OS... though I find that Excel can definitely get bogged down with large spreadsheets on the Mac (and I"m sure similarly with complex formulas, though I don't use much more than sum(...)
 
  #4  
Old 06-07-16, 01:42 PM
Member
Thread Starter
Join Date: Oct 2014
Posts: 643
Received 0 Votes on 0 Posts
Well I ended up getting it done bit by bit. I don't think 2016 is a big improvement over 2011. And it's uglier. Everything Microsoft has put out for the last 15 years has been uglier than the previous version.

Checking the log, the program always crashed when it's memory use hit ~ 1.8gb. Couldn't find any memory limit on the OS side and there was plenty free so I'm guessing it has something to do with the program.
 
  #5  
Old 06-07-16, 02:12 PM
ray2047's Avatar
Member
Join Date: Mar 2006
Location: USA
Posts: 33,597
Received 13 Votes on 11 Posts
Everything Microsoft has put out for the last 15 years has been uglier than the previous version
Have you considered using LibreOffice?
 
  #6  
Old 06-07-16, 06:00 PM
Member
Join Date: Jan 2008
Location: Southeastern Pennsylvania
Posts: 2,948
Received 0 Votes on 0 Posts
Esand1-

I think I know the problem. Excel has its own memory manager and memory limits and your are limited to 2 GB. You probably hit it if you got to 1.8. But you would think there would be a more orderly stop.
 
  #7  
Old 06-11-16, 11:38 PM
WRDIY's Avatar
Member
Join Date: Jul 2012
Posts: 632
Received 0 Votes on 0 Posts
I used to migrate massive amount of MS Excel files into relational databases. From my experience, most MS Excel vs RAM problems are from heavy usage of formulas. Ironically, I know MS Excel spreadsheet key strength is in the formulas but whenever data proceeds beyond 10k rows, I minimize formula usage at all cost and rely more on VBA or Database SQL. In addition, most users tend to use MS Excel as a miniature database. Again, anything less than 10k rows maybe ok but anything more, probably not in my design opinion.

I remembered when I had to migrate an Excel 2003 workbook with 60k rows of data (limitation is 65536 rows) and approximately 42 columns. There were over 20 columns of formula. So if you do the math, that is lots of formulas to update. Opening the thing literally took more than 20 minutes on a fast computer. When you change data, it was a nightmare because the formulas would ripple across the workbook. When they ran their monthly reports off of this thing, it literally took 70 hours each month.

Anyway, as soon as I moved the data to a database (with normalized tables) and gave it ability to join against other data (millions of records), BAM! it cutted down the processing time to 10 minutes. I know you don't want to move it to a new software but in the long run, this may save you the headaches on the performance. You can continue to bandage and piecemeal but over time, it is a maintenance nightmare.

I migrated an MS Excel 2013 workbook last year for a friend. This workbook has over 900k row of data. I also found an issue that MS Excel 2013 has with Freeze Row on massive data. It kept crashing the workbook. As soon as I unfreeze the row, the process was fine. It took me a long time to pinpoint this issue but when I found it, I sent the retrace steps to Microsoft.

If you don't have Oracle, MS SQL Server, Sybase, My SQL, etc..., move the data to an MS Access database. MS Access database is also portable. Use MS Excel as a simple report layer.

What is your total rows and columns of data?

Are you using Freeze Row and is it MS Office 2013 or after?
 
  #8  
Old 06-12-16, 01:43 PM
Group Moderator
Join Date: Oct 2004
Location: WI/MN
Posts: 19,369
Received 54 Votes on 51 Posts
SQL Express is free and can handle a 10 GB database.
 
  #9  
Old 06-13-16, 12:19 PM
Member
Thread Starter
Join Date: Oct 2014
Posts: 643
Received 0 Votes on 0 Posts
Thanks for the info WRDIY. I needed to get one answer (via a vba script repeatedly using solver) out of an existing excel that I probably won't need to do much more with which is why I didn't want bother migrating everything.

For most of what I do at work, I use matlab or Rstudio w/ Amazon EC2

Playing around though it seems turning off freeze row makes a difference. I'll keep that in mind for the future.
 
Reply

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread
Display Modes
 
Ask a Question
Question Title:
Description:
Your question will be posted in: