The xkcd Velociraptor Problem #1
In Substitute, we learn of a substitute teacher’s “real-life” math problems, which include the following:
Ha, ha, funny commentary on a frustrated old substitute math teacher. (Disclosure: my wife is an 8th grade math teacher, and frustrated is often descriptive of her job.)
So how far could you get before you become Velociraptor Chow? Rhett Allain, a science writer at Wired Magazine, shows us how to figure this out in Here’s How to Solve the xkcd Velociraptor Problem With Code. Prof. Allain describes a numerical approach to the problem using some physics and a bit of python code. Essentially you have some initial conditions, such as speed and position of predator and prey, and some boundary conditions, such as acceleration and maximum speed of each. You select a suitably short time increment, compute the speed due to acceleration and distance traveled based on speed, and update the conditions. If the dinosaur hasn’t yet caught the primate, you again increment the time, update the conditions, and so on, until the two participants coincide in space and time.
You could use your best 8th grade algebra (or my wife’s) and compute the solution analytically, but that’s more suited to paper and pencil, not to a computer. And the numerical approach can be applied to many phenomena, physical and other (including financial).
An algebraic solution?
We’ll use Excel to carry out these same calculations, both as a set of worksheet formulas and as a VBA routine.
Approach 1. Using Excel Table with Simple Formulas
Using an Excel table allows you to make iterative calculations easily. Once you get the formulas right, you can then extend the calculations by adding rows to the table.
Below is the initial setup of the velociraptor problem. Calculations are compiled in a table in the top left of the worksheet. Parameters for the problem are listed in a range nearby and are used in definitions for Names that make setting up the problem easier (see definitions below). The chart plots positions of the velociraptor and of the human on the Y axis vs. time on the X axis.
This screen shot of Excel’s Name Manager dialog shows the Names which were defined to facilitate calculations.
Delta_t is the time increment between calculated time points in the table.
XstartH are the initial positions of the velociraptor and of the human.
AccelH are the acceleration for the velociraptor and for the human.
VmaxH are the maximum running speeds for the velociraptor and for the human.
The table contains these column formulas:
Time =IF(ROW()-ROW(Table1[#Headers])=1, 0, OFFSET([@Time],-1,0)+Delta_t)
Vvel (velocity of the velociraptor) =IF([@Time]=0, 0, IF(OFFSET([@Vvel],-1,0)>=VmaxV, VmaxV, MIN(OFFSET([@Vvel],-1,0)+AccelV*Delta_t,VmaxV)))
Xvel (position of the velociraptor) =IF([@Time]=0, XstartV, OFFSET([@Xvel],-1,0)+[@Vvel]*Delta_t)
Vhum (velocity of the human) =IF([@Time]=0, 0, IF(OFFSET([@Vhum],-1,0)>=VmaxH, VmaxH, MIN(OFFSET([@Vhum],-1,0)+AccelH*Delta_t,VmaxH)))
Xhum (position of the human) =IF([@Time]=0, XstartH, OFFSET([@Xhum],-1,0)+[@Vhum]*Delta_t)
This screenshot shows the table populated to an elapsed time of 2 seconds. The human has just reached his maximum velocity, but the velociraptor is still accelerating. The dinosaur has gotten slightly closer to the human.
You can extend the chase by extending the table, simply by clicking on the small angle-iron at the bottom right corner of the tab and dragging it down as far as needed.
Below, the table has been filled down to 6.5 seconds (the rows between 1 sec and 5 sec have been hidden). We see from the chart that the velociraptor has overtaken the human, and I’ve indicated with red text the row in the table where the position of the velociraptor has first passed the position of the human (row 62, 5.9 sec).
This simplistic model doesn’t stop when the velociraptor reaches his dinner. We need to insert some intelligence into the formulas in our table.
Approach 2. Using Excel Table with More Detailed Formulas
Using a more detailed table allows you to calculate the point of intersection of the paths of the velociraptor and the human, and stop the chase at that time.
Below is the initial setup of the velociraptor problem.
There are a few additional columns:
TTime (adjusted time) =IF(ROW()-ROW(Table14[#Headers])=1, 0, IF(OFFSET([@TTime],-1,0)<>OFFSET([@Time],-1,0), NA(), IF([@Xvel]<=[@Xhum], [@Time], (OFFSET([@Xhum],-1,0)-OFFSET([@Xvel],-1,0))/ (OFFSET([@Xhum],-1,0)-OFFSET([@Xvel],-1,0) -[@Xhum]+[@Xvel])*Delta_t+OFFSET([@Time],-1,0))))
XXvel (adjusted position of the velociraptor) =IF([@Time]=[@TTime], [@Xvel], OFFSET([@Xvel],-1,0)+[@Vvel]*([@TTime]-OFFSET([@TTime],-1,0)))
XXhum (adjusted position of the human) =IF([@Time]=[@TTime], [@Xhum], OFFSET([@Xhum],-1,0)+[@Vhum]*([@TTime]-OFFSET([@TTime],-1,0)))
The formula for
TTime does a lot of work. It’s equal to zero in the first data row, it fills with #N/A after the velociraptor reaches the human, and if the velociraptor catches the human during the current time increment, it interpolates to find the precise time that this happens (see the red entries in row 61).
The chart shows the paths of the predator and prey only up to the point of capture, at 5.8375 sec, or 29.325 m from the human’s initial position.
Approach 3. Using Excel VBA
You can use Excel VBA to solve this velociraptor problem, and animate the chart which illustrates the chase. Initial and boundary conditions are found in the small table in columns G and H.
Columns A through E are the calculations, as in the first approach, except the results are not calculated by worksheet formulas, but instead are calculated by VBA and output to the table as values. These values are plotted in the larger chart.
The small table in columns J through L show the initial and current (i.e., in the most recently calculated iteration) positions of human and velociraptor. These values are plotted in the smaller chart.
Reset Chase button runs the
ResetChase procedure (below), which sets conditions back to the start of the chase, by deleting all table rows after the first data row.
Sub ResetChase() Application.ScreenUpdating = False With ActiveSheet.ListObjects(1) Do Until .ListRows.Count = 1 .ListRows(.ListRows.Count).Delete Loop End With Application.ScreenUpdating = True End Sub
Start Chase button runs the
StartChase procedure (below), which starts the chase and runs it until the end.
Sub StartChase() Dim tTime0 As Double, tTime1 As Double Dim xXhum0 As Double, xXhum1 As Double Dim xXvel0 As Double, xXvel1 As Double Dim vVhum0 As Double, vVhum1 As Double Dim vVvel0 As Double, vVvel1 As Double Dim vVmaxH As Double, AccelH As Double Dim vVmaxV As Double, AccelV As Double Dim DeltaT As Double, DDelTT As Double Dim iDelay As Long, iLooper As Long Dim ws As Worksheet ResetChase ' initialize Set ws = ActiveSheet tTime0 = 0 iLooper = ws.Range("Looper").Value2 xXhum0 = ws.Range("XstartH").Value2 xXvel0 = ws.Range("XstartV").Value2 vVhum0 = 0 vVvel0 = 0 vVmaxH = ws.Range("VmaxH").Value2 vVmaxV = ws.Range("VmaxV").Value2 AccelH = ws.Range("AccelH").Value2 AccelV = ws.Range("AccelV").Value2 DeltaT = ws.Range("Delta_t").Value2 ' loop Do tTime1 = tTime0 + DeltaT ' calculate human velocity and position If vVhum0 >= vVmaxH Then vVhum1 = vVmaxH Else vVhum1 = vVhum0 + AccelH * DeltaT If vVhum1 > vVmaxH Then vVhum1 = vVmaxH End If End If xXhum1 = xXhum0 + vVhum1 * DeltaT ' calculate velociraptor velocity and position If vVvel0 >= vVmaxV Then vVvel1 = vVmaxV Else vVvel1 = vVvel0 + AccelV * DeltaT If vVvel1 > vVmaxV Then vVvel1 = vVmaxV End If End If xXvel1 = xXvel0 + vVvel1 * DeltaT ' has velociraptor caught human? If xXvel1 > xXhum1 Then DDelTT = DeltaT * (xXhum0 - xXvel0) / _ ((xXhum0 - xXvel0) + (xXvel1 - xXhum1)) tTime1 = tTime0 + DDelTT xXhum1 = xXhum0 + vVhum1 * DDelTT xXvel1 = xXvel0 + vVvel1 * DDelTT End If ' add new time point data to row below table With ws.ListObjects(1) .ListRows(.ListRows.Count).Range.Offset(1).Value = _ Array(tTime1, vVvel1, xXvel1, vVhum1, xXhum1) End With ' exit if we're done If xXvel1 >= xXhum1 Then Exit Do End If ' build in delay if animation runs too quickly on screen For iDelay = 1 To iLooper DoEvents Next DoEvents ' persist previous loop's data tTime0 = tTime1 vVhum0 = vVhum1 xXhum0 = xXhum1 vVvel0 = vVvel1 xXvel0 = xXvel1 Loop End Sub
As the VBA code runs and data is added to the table, you can watch the chase progress in the two charts. Here is the chase after two seconds.
Here is the chase after four seconds.
And here is the chase at its conclusion.
Same result as the table based approaches, except for the added benefit of watching the animation as the VBA calculations proceed.
VBA: Accuracy vs. Calculation Load
Obviously the accuracy of our numerical solution depends on the size of the time increment we use in our calculations. If we take smaller time increments, we can reduce the error resulting from treating nonlinear behavior (acceleration) as linear. On the other hand, taking smaller time increments means our program has to make more calculations, and therefore it will run more slowly.
I ran a modified
StartChase procedure to determine how time increment size affected elapsed time, distance, number of iteractions, total calculation time, and calculation time per iteration. I turned off screen updating during this procedure and did not output the results of each increment to the worksheet, to ignore the time VBA spends communicating with the Excel worksheet.
Here are the effects of increment time on solution accuracy and calculation time. The blue shaded row shows the solution used in the examples above, an increment of 0.1 sec.
We learn some interesting things if we plot this data.
If we plot computed elapsed time to capture of the human vs time increment (Delta_T), we see a straight line with an almost perfect correlation (below left). The computed elapsed time gets closer and closer to the Y intercept as the time increment decreases. We could say that this Y-intercept is the actual time of capture, and our solutions get closer and closer to predicting it as the error in the incremental calculations is minimized. In fact, the difference between the time increment used above (0.1 sec) and the Y-intercept is only 0.05 sec. Maybe we can decide that our analytical solution using an increment of 0.1 sec is “accurate enough”.
If we plot distance to capture vs time increment (below right), we see a trend, but not a nice linear trend as with the elapsed time chart. However, we see that for time increments of 0.1 sec or shorter, there is barely any deviation from the 0.1 sec computation of 29.325 m.
Naturally, a smaller time increment will result in more iterations, in an inverse relationship. The chart below left shows a linear scatter chart, which isn’t very interesting; the data for a large increment (0.5 s) is found out on the X axis. Shortening the increment brings the points back along the X axis until they curve around and head up the Y axis. What is happening is shown better in the log-log plot below right. This is a straight line, and the exponent on X is -0.9995, extremely close to the inverse 1/X relationship we expect.
We would expect a similar inverse relationship between total calculation time and time increment. The linear chart below left shows the same axis-hugging behavior as above, and the log-log chart below right gives us another straight line. This one isn’t as perfectly straight, but a regression on the points for smaller increments (filled points) has a strong correlation and a nearly 1/X relationship.
For the time increment of 0.1 sec from the examples above, we have a total calculation time of 0.05 sec, which is pretty fast for an “accurate enough” solution. If we really want more accuracy, we could even go to a 1 sec solution based on a o.oo2 time increment.
Keep in mind that these calculation times are for a modified procedure, not the procedure that updates the table and chart after each calculation increment. Our 0.1 sec time increment may give the solution in 0.05 sec, but the time we spend watching each increment update the table and chart is more like 4 or 5 seconds.
Finally, we see that as the number of iterations increases, the calculation time increases linearly.