Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations cowski on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel Iterating
- Thread starter 3b2
- Start date
GregLocock
Automotive
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.
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.
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.
- Thread starter
- #4
- Thread starter
- #5
NormPeterson
Structural
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
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
- Thread starter
- #7
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.
Hydrocarbon base density calculations are based on iteration without using VB. (the spreadsheet, DENS08.xls, is available at
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
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
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?
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?
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
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
GregLocock
Automotive
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.
Cheers
Greg Locock
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
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.
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.
- Thread starter
- #13
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.
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.
- Status