×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Iterating

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

iterate how?

TTFN

RE: Excel Iterating

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.

RE: Excel Iterating

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

RE: Excel Iterating

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

RE: Excel Iterating

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

RE: Excel Iterating

(OP)
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.

RE: Excel Iterating

Hydrocarbon base density calculations are based on iteration without using VB. (the spreadsheet, DENS08.xls, is available at 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
www.ViscoAnalyser.com

RE: Excel Iterating

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?

RE: Excel Iterating

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
www.ViscoAnalyser.com

RE: Excel Iterating

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.

RE: Excel Iterating

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.

RE: Excel Iterating

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources