Solved

Calculation errors in Excel 97 (Massive Workbook)

Posted on 2002-05-28
13
609 Views
Last Modified: 2008-02-01
I am having problems with calculations in my MASSIVE Excel 97 spreadsheet.  The file size is 40 Mb and it takes about 7-8 minutes to load.  I am working on a Win 98SE machine with a 400 MHz AMD K-2 CPU, 256 Mb Micron SDIMM memory, a 128 Mb permanent memory swap file, VIA 503+ motherboard (2 Mb L2 cache) and 16 Mb Matrox G400 video card.  I am not running any AntiVirus software and resources stays about 70-85% (GDI and user heaps).
 
The layout of the workbook is as follows.  The main data crunching sheet imports dates and stock prices in the first two columns.  I then calculate the parameters I am interested in testing in the next 8 columns and then test a potential investment strategy in the next 9 columns based on stock price and the previously mentioned parameters.  I then evaluate another strategy in the next 9 columns, etc.  There are two key columns among the nine and one, which actually says buy or sell, is a series of nested If statements which is about 5 levels deep.  I am testing a total of 16 strategies at each time so this sheet is around a size of 3000 rows by 140 columns.  Hitting calc takes about 20 seconds to run.

With this “basic” spreadsheet” I am varying the inputs to the testing parameters in a series of two For Each…Next loops in the VBA macro.  I input a set of parameters, calculate, and then extract 600 summary data measures to evaluate and Paste Special Values them to a results worksheet (same workbook).  I then change the input variables, calc, and paste the next set of results.  To run the entire macro program takes 20 minutes.  My resource heaps do not drop during the calculation process.

The problem:  I am having questions about the accuracy of the calculations.  When I get through the entire calculation and look at the results I sometime see spurious data.  I then look specifically at that cell and do an Edit / Calc (F2 F9) I get a different (correct) result than what is showing on the spreadsheet.  Also, after I hit calc the status bar will usually show “Ready       Calculate”.  The macro makes used of the status bar to show me progress and I reset it as the last step of the macro.  Also, calculation is always set to Manual.

In an earlier version of the VBA code I used        Worksheets(“Data”).Calculate        to force a calculation and I have replaced that with the simple    Calculate    command – no difference in the error problem.  I have also played with changing Excel / Option / Calculation to use iterations and with settings as high as 100000 times ,  .00000001 test result without any apparent change.  My formulas should not require iterations – they are basic in that the program looks at the data in column B, compares it to data in column G, and then does one of two things.  A pretty black and white decision rule and not like Solver type “how close can we get” type problems.

This is actually the fourth step in my project and I have used smaller spreadsheets of about one quarter-one third the size and number of columns to eliminate a lot of potential stocks.  In these programs I did not have this calculation error question.  I have tried breaking this latest BIG workbook in two pieces and running separately and the problem remains in both smaller pieces.  For my end purpose it is a strong priority to have all the data in one place for comparison purposes.

Does anybody have any ideas on how to tell Excel to really calculate everything.  My guess is the clue is somewhere in the symptom where the word Calculate shows up on the status bar even after I calculate.  Occasionally that does not occur and I SEEM to not have the accuracy questions when that happens (it doesn’t happen frequently enough that I have been able to really test it).  Also, a simple idea of hitting calculate repeatedly when the calculate indicator shows on the status bar does not change anything - the errors remain.

Help please!

Dave
0
Comment
Question by:dbish
  • 4
  • 2
  • 2
  • +5
13 Comments
 
LVL 13

Expert Comment

by:cri
ID: 7040252
Strictly "Off-the-cuff":

a) Circular reference stopping the calculation

b) According to own experience, Excel 'recalculation strategy' can get derailed by a worbook which:
- Uses too many VBA functions defined as volatile
- Dependencies 'criss-cross' wildly the workbook instead of being (predominantly) top/down & left to right

c) Corrupted workbook
0
 

Author Comment

by:dbish
ID: 7040290
To cri:

The first two suggestions are unfortunately not it - CIRCs are cleared up (Excel has been good at prompting). I am using no user defined VBA functions (volatile or otherwise) and most of VBA is simple stuff like put this value here, calc, and copy / paste answer elsewhere.  I do keep a running total on elapsed time (using Timer function to calcuate) which periodically gets shown on status bar.  The workbook flow is very much top down - normal.

Regarding corruption - how would I look for that - is there an export / import trick to fix such a thing??

One additional symptom - I can manually change one of my input parameters (it is moving average factor (INTEGER) which ties to LOTS of cells).  When I change the value it takes quite some time to return control to the keyboard - even though automatic calculation is turned off.  When I do then calculate the time taken is about what it always runs.

Thanks

dbish
0
 

Accepted Solution

by:
krwennerberg earned 50 total points
ID: 7040642
I have been working with Excel spreadsheets exceeding 30 MB with VLOOKUPS, hidden columns, pivots etc. for the past few months.  We find Excel to be unpredictable after 30 MB file size and inordinately slow.  We use 30 as an upper limit, though we have stretched it to 40 MB.  You are pushing the limits of Excel--  its versatility should not be assumed to also be brute strength. Your file size and record count is managable for something such as MS Access.
BTW, for these large Excel files, I recently switched to a new 1.7 gHZ P4 from Dell with 512 MB RAM.  I highly recommend it-- my previous machine was more like yours.
KIM W.
0
 
LVL 3

Expert Comment

by:leojl
ID: 7040746
hi dbish

Your main problem is that you are doing very sophisticated work on an old machine with an old program. Get a new Dell computer with lots of memory and XP and the latest version of Microsoft Office.

It is a waste of time and energy to try to do good work with outdated equipment.

I just bought a Dell Dimension 8200 and it is really great.

sincerely,
leo
programmer at large
0
 

Expert Comment

by:lstaple
ID: 7040866
Here's my two cents (echoing krwennerberg, et al):

Please consider moving your app to Access.  That would solve your performance and reliability problems almost entirely, even if you can't upgrade your hardware just yet.

Good luck!
0
 
LVL 3

Expert Comment

by:leojl
ID: 7040933
hi dbish

Your main problem is that you are doing very sophisticated work on an old machine with an old program. Get a new Dell computer with lots of memory and XP and the latest version of Microsoft Office.

It is a waste of time and energy to try to do good work with outdated equipment.

I just bought a Dell Dimension 8200 and it is really great.

sincerely,
leo
programmer at large
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:dbish
ID: 7041036
I am doing this project at home - it is not for business and I am reluctant to create a money / time expenditure as my project might just not work.  I do not have Access and have never worked with it.  My program has simple VBA but complicated Excel formulas and is more of a spreadsheet than database (based on the little I know).  

I am much more likely to try to solve the problem by breaking the progam into many smaller pieces if there is no answer available on making calculate work (or cri's thought on finding and eliminating corruptions is found).  I don't do much else that would justify the purchase of a new machine.  

I would certainly enjoy new equipment but it just isn't in the cards  (although I do keep buying lottery tickets - "who knows,  Dell Dell Dell" he keeps chanting to himself :)  

dbish
0
 

Expert Comment

by:jlovie@beta
ID: 7048987
Is all the data in one worksheet in the workbook ?

Might it be possible to split it into several separate sheets, & from there save them as separate Excel workbook files which reference each other ?

Not sure if this would solve the problem, but it would avoid having one massive .xls file loaded at any time
0
 

Expert Comment

by:krwennerberg
ID: 7051137
Regarding separate workbooks:
Though breaking up Excel into separate workbooks solves file size problems, this is one of the corners that you can paint yourself into with Excel.  Multi-workbook implementations are onerous to change as your project expands and cumbersome to work with.  I know, because I've built multi-workbook solutions with multi-worksheets within.  Each change/enhancement gets more and more difficult to alter, and eventually the structure dictates your ability to make changes.  Plan ahead-- as one of our wise men here said, "Every solution eventually becomes an enterprise solution, or dies."

KIM W.
0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7060056
dbish

I reduced the points to 50 for a split and refunded the remaining points. Please accept one expert's comment as an answer and post questions for the other experts in this TA with a title like "points for xxx" and a body "For your help with 20305493"

** Mindphaser - Community Support Moderator **
0
 

Author Comment

by:dbish
ID: 7060226
I have tried that and found the linking across woorkbooks takes as much computation time and resources hit (and error occurences) as one big one.

See accepted answer for my solution.

P.S. - I will be giving you some points in a separate question for your help
0
 

Author Comment

by:dbish
ID: 7060239
I was hoping for some slick other method of entering the Calculate command that would involve increased iterations or some such technique to eliminate calc errors.  No such luck so I guess I have to accept defeat.  The file size is certainly the key issue here.  I am awarding you the points (in a lowered amount) for your comments on your experience about your problems with large files.

In the end I had to redesign the entire porject.  My original workbook had a column of stock quotes and dates (col A,B), some interim calculation columns, and then a series of 17 scenarios to test which took 9 columns each.  Only two of the nine columns were different in each scenario.  I ended up scratching the idea of concurrently showing results of all 17 scenarios and running one at a time and just copying the changed formulas to recalulate one scenario and then the next.  Overall calculation time is about the same (10% faster) but I have much higher confidence in quality of end results.  

I still have to grapple with how to show multiple scenarios in one graph and I think I will have to save results (not formulas) in temp workspace and then graph.

Thanks to you and all the experts for your help.

dbish
0
 
LVL 27

Expert Comment

by:Asta Cu
ID: 7278920
Hi everyone....

--->> jlovie  <<---  dbish has posted the following question for you to flow you points for help you gave him here.  Please comment in the link below:

http://www.experts-exchange.com/Applications/MS_Office/Q_20308733.html

":0)  Asta
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now