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).
db says
I hope your daughter gets an A, after all your effort, Jon!
Jon Peltier says
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.
Hadley says
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.
Jon Peltier says
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.
Doug Glancy says
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 :).
Sjoerd Hoogwater says
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!
Jon Peltier says
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.
scott says
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)