Solved

Manipulating an Excel Spreadsheet With Iterative Formulas

Posted on 2010-08-27
8
707 Views
Last Modified: 2013-11-27
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?
0
Comment
Question by:AdvizeIT
  • 5
  • 3
8 Comments
 
LVL 80

Expert Comment

by:byundt
ID: 33544458
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.

Brad
0
 

Author Comment

by:AdvizeIT
ID: 33544687
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
 
LVL 80

Expert Comment

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

Author Comment

by:AdvizeIT
ID: 33545375
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 80

Expert Comment

by:byundt
ID: 33545777
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

Brad
0
 
LVL 80

Accepted Solution

by:
byundt earned 250 total points
ID: 33545900
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.

Brad

0
 
LVL 80

Expert Comment

by:byundt
ID: 33555443
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.

Brad
0
 

Author Comment

by:AdvizeIT
ID: 33567127
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

760 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

17 Experts available now in Live!

Get 1:1 Help Now