Excel Iterating

[3b2]

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?

 

[IRstuff]

iterate how?

TTFN

 

[GregLocock]

Yes you can. If you want to see an iterating spreadsheet that does not use VBA or the solver then download SLARCK.xls from my website.

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.

 

[3b2]

To TFFN: Iterate to a convergence with a tolerance , say 0.0001. Take the result of two calculations and find the difference between them.

 

[3b2]

Greg: I tried your website. You have moved it right? There was no link to be redirected to however.

 

[NormPeterson]

Tools / Options / Calculation tab

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

 

[3b2]

Norm: Tried that. The iteration box is stil checked. I suspect I do not have an appropriate definition for setting up the iteration process. Is there a function available to force an iteration that can be included within a cell? It will probably be nested in an IF function.

 

[jmw]

Hydrocarbon base density calculations are based on iteration without using VB. (the spreadsheet, DENS08.xls, is available at

http://www.ViscoAnalyser.com)

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

http://www.ViscoAnalyser.com

 

[EGT01]

3b2,

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?

 

[jmw]

EGT01,
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

http://www.ViscoAnalyser.com

 

[GregLocock]

3b2 look on the downloads page

Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[EGT01]

JMW,

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.

 

[3b2]

To All: O.K. I got some ideas. I'll follow up on them. Thanks EGTO1 and jmw for the samples. I've created circular references before, a long time ago, but have forgotten what I did. I was using a different spreadsheet program that allowed one to select whether to iterate or not. The calculation is to equal two relationships:

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.