Calculation errors in Excel 97 (Massive Workbook)

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