Solver has been used in numerous and diverse applications (Kemmer and Keller 2010) and is ideally suited to fit data with non-linear functions by iterative algorithm. The algorithm works by minimizing the sum of the squared differences between the observed data points and the function describing the data (Brown 2001).

Biochemical oxygen demand (BOD) is used as measure of the pollution produced by domestic and industrial wastes. The data shown in Table 1 shows BOD data over a range of incubation days (Box et al. 2005). Data was fitted into the following model.

\(Y = {b_1*(1-exp{(-b_2*x)})}\) (1)

Y is the exponential model describing BOD

b₂ is the rate constant (parameter 1)

b₁ is the BOD value (parameter 2)

x is incubation time in days

a) Data is entered in Excel, incubation days (x) in column A and BOD values (y) in column B.

b) Initial values for b₁ and b₂ are set to 100 and 0.75 in cells F2 and F3.

c) Y calculated was obtained by substituting b₁, b₂ and corresponding x values in equation (1).

d) Sum of squared errors (SSE) was obtained by squaring the difference between Yobs and Ycalc.

e) Open Solver (In Excel 2013 it is located Data tab). The dialogue box is shown in Fig. 1.

Figure 1.  

Solver implementation: Objective cell in F4 (minimize sum of squared errors) by changing values of b₁ and b₂ in cells F2 and F3 respectively.

f) Solver also option of showing the iteration results (Refer to Fig. 2,click options in solver dialogue box). The detailed explanation for controlling solver features are described (Brown 2001).

Figure 2.  

Solver options tab.

g) When the show iteration results is selected the following message is displayed, it prompts the user either to continue, stop or save scenario (Refer Fig. 3).

Figure 3.  

Solver message box showing iteration result message.

h) The user has to manually select an option to show results of each iteration. This would considerably increase the time for reaching an optimal solution.

i) The results obtained for parameters b₁, b₂ and SSE after solver implementation matched with the reported values (Box et al. 2005).

The worksheet change event macro (as shown in Fig. 4) is triggered, when the value in either cells F2 or F3 changes, then these values are copied and are pasted in columns J (b₁), K (b₂) and L (SSE) respectively. The values of b₁, b₂ (top) and SSE (bottom) for each iteration during the solver optimization are plotted in Fig. 5. The Excel macro file is included in Suppl. material 1

Figure 4.  

Excel Worksheet Change event macro to copy result from each solver iteration.

Figure 5.  

Plot showing the values of b₁, b₂ (top) and SSE (bottom) for each iteration during the solver optimization.

This article demonstrates the use of Excel worksheet events to obtain the objective cell values after each iteration. The number of objective cell values obtained is dependent on the constraint precision value.