Research Ideas and Outcomes : Research Idea
Research Idea
Use of Worksheet events in Excel to save solver objective cell value from each iteration
Prasanth Sambaraju
 Corresponding author: Prasanth Sambaraju (prashanth.kng1@gmail.com) Academic editor: Editorial Secretary Received: 07 Dec 2021 | Accepted: 22 Feb 2022 | Published: 25 Feb 2022 © 2022 Prasanth SambarajuThis is an open access article distributed under the terms of the Creative Commons Attribution License (CC BY 4.0), which permits unrestricted use, distribution, and reproduction in any medium, provided the original author and source are credited. Citation: Sambaraju P (2022) Use of Worksheet events in Excel to save solver objective cell value from each iteration. Research Ideas and Outcomes 8: e79006. https://doi.org/10.3897/rio.8.e79006

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.

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

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

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

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

• Box GP, Hunter JS, Hunter W (2005)
Statistics for Experimenters: Design, Innovation, and Discovery
.
Second
.
Wiley
• Brown AM (2001)
A step-by-step guide to non-linear regression analysis of experimental data using a Microsoft Excel spreadsheet
.
Computer Methods and Programs in Biomedicine
65
(
3
):
191
200
. [In
English
]. https://doi.org/10.1016/s0169-2607(00)00124-3
• Kemmer G, Keller S (2010)
Nonlinear least-squares data fitting in Excel spreadsheets
.
Nature Protocols
5
(
2
):
267
281
. https://doi.org/10.1038/nprot.2009.182

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.