Abstract
Parametric modeling requires parameter estimation which, in turn, requires optimization. For study, for instruction, and for practical road safety modeling one can use an optimization tool available in Excel: the Solver.
The use of the Solver is explained and its application to curve-fitting illustrated. While the Solver is a robust tool two problems may cause it to fail. First, while Solver is good at converging on local optima, finding the global optimum is a challenge. The “correspondence graph” can help alleviate this problem. Second, the algorithm may fail for computational reasons when the parameters to be estimated differ by several orders of magnitude. This problem may be obviated by appropriately scaling the model variables.
This is a preview of subscription content, log in via an institution to check access.
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
Notes
- 1.
The Solver comes with Excel but has to be installed and referenced. Installation instructions may be found at http://office.microsoft.com/en-us/excel-help/introduct. Before its first use the “Excel Solver” has to be “referenced.” To do so, go to “Developer,” on the “Code” tab go to “Visual Basic,” and in the window that opens click on “Tools.” On the menu select “References,” check the “Solver” box, and then click OK.
- 2.
- 3.
The term “objective function” is associated with some real setting in which an increase in the function is thought either desirable or not desirable. This is why there is interest in finding that combination of variables which make the objective function either largest or smallest.
- 4.
To download this spreadsheet go to http://extras.springer.com and enter the ISBN of this book. Look in the “Spreadsheets” folder for “Chap. 5. How to use the Solver.xlsx.”
- 5.
The “Target Cell” in Solver is equivalent to “Objective Function” in optimization.
- 6.
If a function has more than one peak or valley they are its “local extrema.” The lowest of the local minima or the highest in the local maxima is the “global optimum.” When the parameters of a model equation are to be estimated we are interested in the global optimum. The task of global optimization of non-convex function remains difficult. The search for efficient algorithms to find global optima is an active branch of research in applied mathematics. An e-sourcebook of what is known is by Weise (2008). The standard Microsoft Excel Solver and Premium Solver Pro do not offer built-in facilities for solving global optimization problems.
- 7.
To increase the Solver’s chance of converging on a globally optimal solution the following two-step strategy can be tried: (1) Begin the curve-fitting by minimizing the sum of squared differences which is known to be convex. (2) Use the solution from step 1 as a starting point for any other objective function.
- 8.
In mathematics a singularity is a point at which a given mathematical object is not defined or not well-behaved.
- 9.
This result is obtained by Excel 2007. Newer versions of Excel correct some errors of this kind automatically and thereby prevent making an instructive point.
- 10.
To download this spreadsheet go to http://extras.springer.com and enter the ISBN of this book. Look in the “Spreadsheets” folder for “Chap. 5. Fitting a curve to estimates of sigma mu.xls or xlsx.”
- 11.
These differences will also also be called “residuals.”
- 12.
Ideally the weight should be inversely proportional to the variance of the data point.
- 13.
Now, at optimum, β0 = 0.59.
References
Fylstra D, Lasdon L, Watson J, Waren A (1998) Design and use of the Microsoft Excel Solver. Interfaces 28(5):29–55
Harmon M (2011) Step-by-step optimization with Excel solver. The Excel Statistical Master. http://excelmasterseries.com/D-_Loads/New_Manuals/Step-By-Step_Optimization_S.pdf
Weise T (2008) Global optimization algorithms – theory and application http://www.it-weise.de/projects/book.pdf
Author information
Authors and Affiliations
Rights and permissions
Copyright information
© 2015 Springer International Publishing Switzerland
About this chapter
Cite this chapter
Hauer, E. (2015). Preparing for Parametric Curve-Fitting: The “Solver”. In: The Art of Regression Modeling in Road Safety. Springer, Cham. https://doi.org/10.1007/978-3-319-12529-9_5
Download citation
DOI: https://doi.org/10.1007/978-3-319-12529-9_5
Published:
Publisher Name: Springer, Cham
Print ISBN: 978-3-319-12528-2
Online ISBN: 978-3-319-12529-9
eBook Packages: EngineeringEngineering (R0)