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?
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.
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.
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?
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.
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: