# Manipulating an Excel Spreadsheet With Iterative Formulas

We're writing a .NET 2.0 app which accesses and manipulates an Excel spreadsheet using TMS Flexcel Studio (the target computers might not have Excel installed).  The spreadsheet makes use of iterative formulas which have circular references.  This is fine if you're using Excel itself as it has an option to turn on iterative caculations.  However changing values via a .NET component causes the formulas to stop updating after the first change presumably because of a circular reference error.  Is there a solution out there which would allow iterative calculation to work?
###### Who is Participating?

Commented:
The product has been renamed Calc4Web http://www.calc4web.com/brochures.htm# Here is a link to the price sheet. http://savvysoft.stores.yahoo.net/turboexcel.html
The Professional version at \$3599 is required if you want to change VBA code into royalty-free distributables. You can use less expensive versions if you don't need the VBA.

A free three-day trial version is available.

0

Commented:
The more specific you can make the question, the more likely you are to get a solution to it. Although I have solved this general type of problem (convergence) many times in my career, the best method(s) always depend on the details of the situation at hand.

Possibility 1:
Repeat the calculation using a set of cell pairs, one pair for each round of iteration
Guess 1           Result 1
Guess 2           Result 2      (Guess 2 depends on Result 1)
Guess 3           Result 3      (Guess 3 depends on Result 2)

Possibility 2:
Perform the iterative calculation in .Net, then report the results back to the spreadsheet

Possibility 3:
Rewrite the Excel formulas so they don't need iteration. This is not always possible, but there are some awfully clever people active in the Excel Zone.

Possibility 4:
Use a Data Table to vary the guess, repeat the calculations, and report the result. Your answer won't converge completely, but you can get pretty close. I don't know if TMS Flexcel Studio supports this approach--but Excel certainly does.

Possibility 5:
Use Solver (or Goal Seek) to force the convergence. Once again, I don't know if TMS Flexcel Studio supports this approach--but Excel certainly does.

0

Author Commented:
We're putting a front end to a spreadsheet that has hundreds of calculations, many of them iterative. We've asked our client (an engineering firm) if the formulas could be rewritten but they've indicated that this is not possible and moreover the calculations are a trade secret.  I know we're being painted into a corner but I'm looking for solutions that will allow us to use the existing spreadsheet.
0

Commented:
I'm an engineer and have solved many iterative engineering problems with spreadsheets. What type of problem are they solving?
0

Author Commented:
They offer HVAC systems.  Different parameters are entered into the spreadsheet (Flow rate, Antifreeze %, Water Temp, Air Temp, Compressor Type, etc.) and the spreadsheet outputs things like BTU/h, Watts, and other stats.  The engineers understand all the calculations, we don't.  The spreadsheet is working fine when opened and used in Excel but the goal is to remove the Excel dependency on client computers.
0

Commented:
I know exactly what type of problem you are talking about.

If you look at my member profile, you will see a description of a "counting snowflakes" problem. In that problem, I needed to model the performance of antifreeze solutions, solution properties (enthalpy & temperature), refrigeration compressor performance, air temperature, flow rate, etc.). This was for equipment being sold into the HVAC market. There were three nested loops that each needed to converge to get a single performance point (BTU/hr, Watts electric power required, snowflake inventory, etc). Solving the overall problem required getting hour by hour performance of the equipment, and varying the inputs (under macro control) to get the best design.

I ended up performing all the calcs in about 50 pages of VBA, and using Excel to handle inputs and outputs. Back in 1995, it took about 30 seconds to simulate a week of system operation--and it took a lot of algorithm optimization to get the calc time down to that level. The same program today takes just a few seconds (computers are much faster).

If you don't understand the thermal design aspects of the problem, you will have difficulty optimizing the solution. If your client doesn't understand programming, he is going to have difficulty as well.

Given your client's goal of removing Excel dependency, you might take a look at TurboExcel by SavvySoft. It turns the Excel spreadsheet into C++ code which can then be compiled and run completely independent of Excel. I haven't used it (the \$4000 cost was too much for me to justify), but it certainly seems to address your client's needs. http://www.turboexcel.com/faq.htm

0

Commented:
Another approach you might take if the convergence is very rapid is to use a set of three or four identical worksheets. These sheets would be linked such that the initial guess is made on Sheet1, a revised guess (based on Sheet1 results) in Sheet2, an even better guess (based on Sheet2 results) in Sheet3, and the final answer on Sheet4 with confirming calcs (based on Sheet3 results). Worksheets Sheet1, Sheet2 and Sheet3 could be hidden. In essence, this is a four iteration solution done without needing to turn iterations on.

From past experience with heat and mass transfer problems (what I assume your customer is doing), three or four iterations are plenty if you have a good means of guessing the inputs to the next iteration. You should be able to converge within a few tenths of a percent.

0

Author Commented:
Thanks.  I'm not sure if we can use this solution as we have other .NET components bolted on (replication of user controls found in spreadsheet, ability to generate PDF order forms from the output, ability to email) and the system actually consists of two workbooks (a UI workbook and a "calculation engine" workbook that changes on a periodic basis) but we'll look at Calc4Web.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.