Nemaplot hyperspectral data analysis and population modellingEvaluation reinvented

 

Modeling dynamical processes with differential equations (ODE's) and parameter fitting
by using the Excel® Solver

Example demonstration

We start with a simple straightforward, pharmacokinetically orientated, but arbitrary example, a three compartment model with three non-linear fluxes, the content of compartment I (Infusion) with an initial value of 100% decays with the rate rd, the 2nd compartment D (Digestion) is filled up by the rate rd and depleted by a rate rk and additionally a loss term rl, compartment 3 B (Blood concentration) is filled up with the rate rk.

Fig. 1: Construction of a three compartment model
Eq. 1,2,3: ODE's of the three compartment model

The VBA Code of the model is straightforward, the array y contain the ODE's:
Sub ODE(y#(), dy#())
dy(1) = -rd * y(1)
dy(2) = rd * y(1) - rk * y(2) - 0.01 * y(2)
dy(3) = rk * y(2)
End Sub

The model above has been added to the VBA code of the numerical ODE integrator (RKF method). The following example demonstrate how the parameter estimation problem is handled by simply varying the related parameter manually:
Video 1: Manual fitting by slider, first step, adjusting the first rate parameter, followed by the second step, fitting the second parameter, until a sufficient agreement is achieved. Fitting criterion is the sum of the three R2- values of each single fit. Watch cell S31 (RCtot) for progress

The following example demonstrate how the parameter estimation problem is handled by the Excel Solver.
Video 2: Fitting by Solver, necessary steps: addressing the target cell, select the parameters to be varied, choose the fitting method. Start Solver, accept the solution. In case the screen is not refreshed automatically, rerun the model with the new parameter values

The last example demonstrate the advantage of adding the Solver functions to the VBA code.
Video 3: Fitting by Solver, control by VBA code

The examples demonstrate one possibility , how to realize both the calculations of ODE's and their parameter estimations in an Excel sheet by the combination of a numerical integrator and Excel Solver. As the chosen ODE's have an analytical solution, the fitting would be possible even without the RKF.

back Back to ODE introduction Continue to ODE qualtiy check forward



Accept

Nemaplot uses cookies to provide its services. By continuing to browse the site you are agreeing to our use of cookies. More information (in German only)