In Graphical Approach to a Simple Physics Problem and Regression Approach to a Simple Physics Problem I have discussed approaches to solving my daughter’s physics homework. Basically, the problem started with this data, showing the time to drain water through a hole in a bucket, based on the diameter of the hole and the initial height of water in the bucket.
In the earlier two posts, I found that the drainage time was related to hole diameter through a power law relationship with an exponent of -2, and to height with an exponent of 1/2.
Often when modeling a physical process you have theoretical relationships that describe the process, and you just need to find the appropriate factors in the formula. Then you can manually plug and chug to find these factors, or you can use an analytical tool to find them.
In Goal Seek – Optimization Approach to a Simple Physics Problem I showed how to use the built-in Goal Seek optimization tool to determine the coefficient to the power law function involving initial water height and hole diameter. Excel has another tool, an optional add-in called SOLVER, which is a more powerful optimization tool.
I have set up my data as shown below. The initial data is in C9:F12. In C3:F6 I have calculated values for a formula of the form
t = K * ha * db
where t is time to drain the bucket, h is the water height, d is hole diameter, K is the unknown coefficient, a is the exponent -2, and b is the exponent 0.5. I put a provisional factor K in cell A1, based on the preliminary regression work. Cell C3 has this formula, which is copied into the whole range C3:F6:
=$A$1*C$2^0.5/$B3^2
Finally, C15:F18 tabulates the squared differences between the calculations and the actual data. The formula in C15 is simply
=(C3-C9)^2
Cell A16 contains the sum of all of these differences (sum of squares). This is more routinely used than the simple sum of differences used in the Goal Seek approach, but the final answers are close.
I used SOLVER to minimize the sum of the squared differences in A16 by changing the factor in cell A1. Unlike Goal Seek, SOLVER actually does find a minimum.
From the Tools menu, choose SOLVER (in Excel 2007, on the Data ribbon tab, click on Solver in the Analysis group). If the command for SOLVER is unavailable, you will have to install it. See Installing an Excel Add-In for instructions; the SOLVER add-in is either present in the list on the Add-Ins dialog, or you can click the Browse button and look in the SOLVER folder.
The following dialog allows you to indicate which cell to change in order to set another cell to a particular value. We are not using the powerful capabilities of SOLVER to include constraints in the model.
Click OK, and SOLVER does its magic, and shows a dialog which either shows a solution or explains why no solution was found. SOLVER did find a solution to this problem.
Here is the complete SOLVER solution.
These charts show the SOLVER solution (markers and dotted lines) in comparison to the original data (solid lines).
Leave a Reply