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 the following 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 or empirical relationships that describe the process, and you just need to find the appropriate factors in the formula to fit your specific set of conditions. Then you can manually plug and chug to find these factors, or you can use an analytical tool to find them.

Excel has a built-in tool called Goal Seek, which I will use to find the coefficient to the power law function involving initial water height and hole diameter.

I have set up my data as shown. 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 differences between the calculations and the actual data. The formula in C15 is simply

=C3-C9
 

Cell A16 contains the sum of all of these differences.

I used Goal Seek to minimize the sum of the differences in A16 by changing the factor in cell A1. Goal Seek doesn’t actually find a minimum, but if you enter zero, it will get as close as it can.

From the Tools menu, choose Goal Seek (in Excel 2007, on the Data ribbon tab, click on What-If Analysis in the Data Tools group, and select Goal Seek). The following dialog allows you to indicate which cell to change in order to set another cell to a particular value.

Click OK, and Goal Seek does its magic, and shows a dialog which either shows a solution or explains why no solution was found. Goal Seek did find a solution to this problem.

Here is the complete Goal Seek solution.

These charts show the Goal Seek solution (markers and dotted lines) in comparison to the original data (solid lines).

Possibly Related Posts:

• SOLVER - Optimization Approach to a Simple Physics Problem
• Graphical Approach to a Simple Physics Problem
• Regression Approach to a Simple Physics Problem
• Choosing a Trendline Type
• Rolling Wheel Animation
• Physics Lesson
• The Purpose of Charting
• Ballistics Animation