Excel Iterating
Excel Iterating
(OP)
I would like to get some confirmation about my suspicion that there is no way to get Excel to automatically iterate cell calculations without using a VBA function. Solver is not an option for this spreadsheet. Can anyone confirm this?





RE: Excel Iterating
TTFN
RE: Excel Iterating
http://www.geocities.com/greglocock/index.html
as to quite how it works, well, that's for me to know, and you to find out.
Cheers
Greg Locock
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Excel Iterating
RE: Excel Iterating
RE: Excel Iterating
Check the 'Iteration' box and define the iteration parameters 'Maximum Iteration' and 'Maximum Change'.
For some reason unknown to me, the 'Iteration' check mark can get lost and may need to be set again.
Norm
RE: Excel Iterating
RE: Excel Iterating
The default settings I have used are:
check the iteration box
check the automatic caclculation box
set maximum iterations 1000
set maximum change 0.0001
This seems to work fine.
The actual calculation does not use VB though some of the settinsg do (IF function and drop down boxes)
JMW
www.ViscoAnalyser.com
RE: Excel Iterating
I know you can iterate to reach a solution if you have an equation that can not be written explicitly for the variable. For example,
x = function(x)
then you can create a circular reference to iterate to find the solution.
To give you an idea of how that works, here's an example of one that I had setup. It's an ugly equation but it was the quickest I had to give as an example.
x = ( 1302.3 + LN(((14.7 + x)/14.7)^2) ) * 0.086436 / (29.4 + x)
Copy and paste the following into cells J10 and J11 respectively and make sure the iteration option is checked.
=J11
= ROUND(( 1302.3 + LN(((14.7 + J10)/14.7)^2) ) * 0.086436 / (29.4 + J10), 3)
Is it possible to setup your problem to create a circular reference?
RE: Excel Iterating
I get a cirular reference warning and follow the prompt to accept this, then set the options for automatic calculation and iteration and that gives me an answer of 3.43 in both cells.
This is typically how the base density calculation works, using the original density as a first approximation and then iterating:
Base density=line density/(EXP((-(alpha 15)*(line temperature-15))*(1+(0.8*(alpha 15)*(line temperature-15)))))
alpha 15=(K0+(K1*(Base density)))/((base density)*(base density))
where K0 and K1 are constants for the type of hydrocarbon. (excuse me not using TGML) and where density is kg/M3 and temperture is degC with 15degC as the reference temperature.
It is usefual that the reverse API calculation is a one shot calculation; i.e. to calculate line density from base density.
These all work fine so 3b2, if you are not getting an iteration then maybe it is your set up of options/calculations?
IS your cacluation one you can post?
JMW
www.ViscoAnalyser.com
RE: Excel Iterating
Cheers
Greg Locock
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Excel Iterating
Thanks for the feedback. The number you found, 3.43, is the number you should have gotten.
3b2,
There was another approach I intended to mention in my last post. I know you say Solver is not an option but what about Goal Seek, is that an option? I find Goal Seek a little easier to use and it seems to work well for cases where I'm comparing the results of 2 equations and trying to minimize the difference.
RE: Excel Iterating
Lo(1 + H/AEas)-2(H/w)sinh(wS/2H)= Delta Convergence
H is the variable to be found by iteration. Solver could do this but I want to have this happen automatically for use by other functions.
Greg. I'll check this out also.
I have some food for thought now and will check back for any other ideas.