Goal Seek – Optimization Approach to a Simple Physics Problem
by Jon Peltier
Thursday, September 11th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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 0.5, and b is the exponent -2. 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).
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
- Ballistics Animation
- The Purpose of Charting
- How to Make a Donut-Pie Combination Chart
- Virtual Globes
Posted: Thursday, September 11th, 2008 under Data Techniques.
Comments: 8
Comments
Comment from db
Time: Thursday, September 11, 2008, 4:17 am
I hope your daughter gets an A, after all your effort, Jon!
Comment from Jon Peltier
Time: Thursday, September 11, 2008, 6:35 am
Dermot – She finished days ago: she’ll get her A with or without me. She asked one or two questions and it was all she needed.
I saw the problem as a good example for a bunch of Excel topics. I’ll have posted five times based on that problem. She thinks I’m a geek.
Comment from Hadley
Time: Thursday, September 11, 2008, 9:09 am
Jon, shouldn’t you be minimising the sum of the _squared_ differences (or at least the absolute differences)? If you minimise the sum of the raw differences you can end up with a situation where you have equal numbers of large positive and large negative residuals – the sum is zero, but the fit is very bad.
Comment from Jon Peltier
Time: Thursday, September 11, 2008, 9:29 am
Hadley -
This is a good point, and one I thought about after posting this example using Goal Seek. In the next example, which uses Solver to do the same thing, I’ve switched to minimizing the sum of the squared differences. The data are pretty well behaved here, but perhaps I should go back and change this example as well.
Comment from Doug Glancy
Time: Thursday, September 11, 2008, 12:08 pm
Jon, Thanks, this topic is very timely to do some work I’m looking at on stormwater retention.
“She thinks I’m a geek.” So do I – it’s what I like most about you :).
Comment from Sjoerd Hoogwater
Time: Thursday, September 11, 2008, 2:12 pm
It seems like your on to a fun project. I wonder why you use markers for the regressed data in your last graph instead of using them to plot the measured data.
Clearly, the measurement error is largest for the fastest drain times (large height, big hole).
As an engineer, I couldn’t resist checking the empirical formula that you have found. Force balance equations suggest that the flow rate is proportional to the square of the diameter, and the square root of the liquid height. Also, the volume in the bucket is the surface area times the liquid height (I assume that the sides are straight to make it easy). The drain time is the integral of the volume divided by the flow rate, where the liquid height changes from h0 (initial height) to zero: t = integral of V/Q = integral of [ A / ( c *d²*sqrt(h)) dh ] which results in t = 2*A/c / d² * sqrt(h), exactly the exponents that you found. The textbook value for c in this equation is 0.2087 l/min if d is in mm and h in metres. If I plug it into Excel, the diameter of your bucket is 20 cm, assuming that you gave times in minutes, diameter of the hole in cm, and water height in centimeters.
I am glad that physics where you live seems to be the same as the physics elsewhere on the planet!
Comment from Jon Peltier
Time: Thursday, September 11, 2008, 2:32 pm
I told my daughter that a little calculus could solve the problem; she’ll be glad to hear that someone supplied the analytical solution for me.
We assumed that times were in seconds and dimensions (the data was printed out and handed to the class). If times were measured using a stopwatch, then fumbly stopwatch fingers would result in greater percentage errors for the shorter tests.
Comment from scott
Time: Saturday, February 7, 2009, 1:30 am
Dude, the orifice equation! Find the flow rate based on the head, subtract from bucket volume at discrete time intervals and graph. You shoulda been a civil engineer.
Q=Cd*a*sqrt(2gh)
where Q = flow (cubic metres per second)
Cd = coefficient of discharge
A = area of orifice (square metres)
g = acceleration from gravity (9.81 m/s/s)
h = head acting on the centreline (m)
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.