Research Ideas and Outcomes : Research Idea
PDF
Research Idea
Use of Worksheet events in Excel to save solver objective cell value from each iteration
expand article info Prasanth Sambaraju
‡ Independent Researcher, Hyderabad, India
Open Access

Abstract

Solver is a Microsoft Excel add-in program which is used to find an optimal value for a formula in the objective cell. Solver accomplishes this either by maximizing, minimizing or setting the objective cell value to a specific value. The article presents the utility of in built worksheet events in Excel VBA to save the value of objective cell from each iteration when solver is used for optimization.

Keywords

Solver, Worksheet events, Iteration

Introduction

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).

Methods

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.

Table 1.

BOD Data.

Incubation x (days)

BOD (mg/L) y

1

109

2

149

3

149

5

191

7

213

10

224

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

Y is the exponential model describing BOD

b2 is the rate constant (parameter 1)

b1 is the BOD value (parameter 2)

x is incubation time in days

Solver Implementation

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

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

c) Y calculated was obtained by substituting b1, b2 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 b1 and b2 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 b1, b2 and SSE after solver implementation matched with the reported values (Box et al. 2005).

Excel VBA to get result from each iteration

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 (b1), K (b2) and L (SSE) respectively. The values of b1, b2 (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 b1, b2 (top) and SSE (bottom) for each iteration during the solver optimization.

Conclusion

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.

References

Supplementary material

Suppl. material 1: BOD data 
Authors:  Prasanth Sambaraju
Data type:  Excel macro enabled file
Brief description: 

BOD data and macro to save each objective cell value during solver implementation.