complex repetitive calculations probllem

  • Thread starter Thread starter pjchausoul
  • Start date Start date
P

pjchausoul

Hello people,
I have 2 problems and i dont know if they can be automated i
excell.Calculations are set up to simulate a process that happens in
capillary.The capillary is segmented and the variables of thes
segments represent cells in excell.The row of values at the startin
point is defined by me,and they serve as the input for the firs
calculation, then the results of this calculation, serve as the inpu
for the next calculation etc.Using a new row for every calculatio
would generate values in all the available cells on a sheet.My goa
however is to see how the data has changed after thousands o
calculations.I want to know if there is a way of storing the result o
one calculation in a separate temporary row and subsequentl
copying/overwriting the previous input and calculating new data fro
that. And i also need an iterative way to solve a cubic function tha
has no formulaic solutions. Preferably one that can be entered a
formulas in the sheet
 
I must say I do not completely understand your questions. I will try,
however, to give you some hints. Please post again if you're still puzzled.
Using one formula to generate an array of results can be done using the
Table command. Refer to Help for details; it may take some time to fully
understand the way it works.
Iterating to a defined result van be done using Tools>Goal Seek. In very
complex situations you may want to look at Tools>Solver. If you can't see
Solver, load it via Tools>Add-ins, check Solver. Solver will also give you
options to see how the values of the variables have moved to the end result.

For a self-iterating worksheet function you may want to have a look at an
example, which has to be adjusted to your specific needs. It is listed below
and can be pasted into a General Module (not a Sheet Module or a Workbook
Module)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

' ===================================================================
Option Explicit

Function Backward(ValueToBeFound As Double, MoreArguments As Double, _
Optional ReasonableGuess, Optional MaxNumberIters, _
Optional MaxDiffPerc) As Double

' This example function goalseeks another function,
' called Forward. It works for almost any continuous function,
' although if that function has several maximum and/or minimum
' values, the value of the ReasonableGuess argument becomes
' important.
' It calculates the value for ReasonableGuess and for
' 1.2 * ReasonableGuess.
' It assumes that the function's graph is a straight line and
' extrapolates that line from these two values to find the value
' for the argument required to achieve ValueToBeFound.
' Of course that doesn't come out right, so it does it again for
' this new result and one of the other two results, depending on
' the required direction (greater or smaller).
' This process is repeated until the maximum number of calculations
' has been reached, in which case an errorvalue is returned,
' or until the value found is close enough, in which case
' the value of the most recently used argument is returned

Dim LowVar As Double, HighVar As Double, NowVar As Double
Dim LowResult As Double, HighResult As Double, NowResult As Double
Dim MaxDiff As Double
Dim NotReadyYet As Boolean
Dim IterCount As Integer

If IsMissing(ReasonableGuess) Then ReasonableGuess = 1.5 ' use default
values
If IsMissing(MaxNumberIters) Then MaxNumberIters = 20 ' that make
sense in the
If IsMissing(MaxDiffPerc) Then MaxDiffPerc = 0.001 ' context of the
function

MaxDiff = ValueToBeFound * MaxDiffPerc
NotReadyYet = True
IterCount = 1
LowVar = ReasonableGuess
LowResult = Forward(LowVar, MoreArguments)
HighVar = LowVar * 1.2
HighResult = Forward(HighVar, MoreArguments)

While NotReadyYet
IterCount = IterCount + 1
If IterCount > MaxNumberIters Then
Backward = CVErr(xlErrValue) 'or some other
errorvalue
Exit Function
End If
NowVar = ((ValueToBeFound - LowResult) * (HighVar - LowVar) + LowVar _
* (HighResult - LowResult)) / (HighResult - LowResult)

NowResult = Forward(NowVar, MoreArguments)

If NowResult > ValueToBeFound Then
HighVar = NowVar
HighResult = NowResult
Else
LowVar = NowVar
LowResult = NowResult
End If

If Abs(NowResult - ValueToBeFound) < MaxDiff Then NotReadyYet = False
Wend
Backward = NowVar
End Function

Function Forward(a As Double, b As Double) As Double
' This is just an example function;
' almost any continous function will work
Forward = 3 * a ^ (1.5) + b
End Function
' ===================================================================
 
I was afraid people couldnt understand the problem fully. It's the 100
character limit , that forced me to be very compact in my explanatio
of the problem. I'll make a few posts in which ill try to explain m
problem more fully.

Situation

in excell i have a row of values which represent the values of a rea
life situation. These values are the concentrations of ions in
solution in a capillary column. I segmented the capillary and relate
every segment with a cell in the row of values. This enables me to kee
track of the concentrations of every segment.
Then i defined various formula for calculating how these concentration
change after short time intervals. So in effect i'm trying to simulat
the physical process. But the problem is that the results of on
calculation serve as the input for the next calculation
 
Problem 1

I tried to perform the calculations in the following manner :

first row is a set of values predetermined,
in the second row there a formulas that used values from first row an
calculates new values,
Same for third row , but values from second row
etc.

after one thousand calculations i have 1001 rows of values which ar
all linked to this first set of values.

I'd like a more compact way of repeating calculations, i thought of
solution , but i dont know how to enter it into excell. It would wor
like this :

Row 1 : predetermined values

These values serve as the first temporary input in row 2, and have t
be copied to row 2

Row 2 : Temporary input (values)
Row 3 : Temporary output (formula's)

In row 3 the result of the 1st calculations have to be transported t
row 2 for the next calculations. So maybe copying/overwriting th
values that were present before

Row 4 : Final outpu
 
Problem 2

one of the calculations involves solving a complex function , i kno
how the solver add-in works and used it manually , but i need so solv
this equation thousands of times. That would be much to time consumin
so i need an automated manner. The function looks something like thi
:

A = x ( (b*d)/(1+e*x) + (f*g)/(1+h*x)

This function produces a qubic function with only real solutions , s
it doesnt have a formulaic solution. Believe me i've checked !

All variables except x are known. So what i'm looking for is a
iterative way of determining x. I know that x has to be positive , an
that all the other variables are positive as well.

So what i need is a way of automating this calculations using formula
in cell
 
Back
Top