I have created an Excel 2007 workbook that numerically solves an integral based on user input values. The tricky part is that I want answers for 10 user-specified locations and the final answer (for each of the 10) depends on an iterative calculation (guess the answer, use that to calculate the answer, iteratively adjust the guess until it matches the calculated, done with GOALSEEK).
Because I have two input variables (the location and guessed answer for that location), I used TABLES to allow the user to input their preferred X locations and use a macro to run GOALSEEK until "guess" = "calculated").
Everything works fine in Excel 2007. When my users that have Excel 2003 try to run the macro, it correctly solves the first 30 to 40 terms in the 100-item table, then returns #num for the remainder. How far into the 100-item table it calculates successfully is a function of the input values.
I have used the debugging option to step through the simple calculation for each cell in the TABLE and it works fine until the very last, simple, subtraction, then returns #num. I am not aware of any problems occurring in the 'behind-the-scenes' numerical integration worksheets and believe the problem is associated with a backwards compatibility problem from 2007 to 2003 with some combination of my use of TABLES, GOALSEEK, and the macro.
I posted a wordier version of this question on the OzGrid forum last week, but didn't get an answer--please help! http://www.ozgrid.com/forum/showthread.php?t=141098
Bookmarks