Speeding saves time but it also can put yourself and other people in harm’s way. But how about if you only speed 5 miles over the speed limit? What about 10 miles? Below, I’ll do a sensitivity analysis in Excel to calculate just how much time you are saving by speeding over various time intervals.
Setting up the file and creating the formulas
I’m going to create a base value of 50 mph to serve as my default speed. I will also create a variable for the interval to determine the different rates I want to move by for my sensitivity analysis. Here is what it looks like thus far:
The formulas for the actual speed are off to the right. I am just taking the previous speed (or in the case of the first value, the default speed) and incrementing it by the interval. Doing it this way will make it easy if I want to adjust the interval or base speed variables without having to manually update the other values.
Next, I need to set up my calculation to determine the time that is saved. At 55 mph, over the course of an hour, I will have traveled 5 miles more than if I was traveling 50 mph (let’s assume this is the legal, posted rate). And since 5 miles is 10% of the 50mph I would be going on an hourly basis, that equates to 6 minutes (10% of 60 minutes) of additional driving that I would do at the posted rate. That is the time saved by speeding at a rate of 55 mph. To put this into a calculation, I first need to take the difference in speed:
C6 is the first value in the speed column and C2 is the default speed. I also need to divide this by the default speed to get the % of an hour this would represent:
The next step is to multiple all of this by 60 (number of minutes in an hour) to convert this into minutes:
Now, I can copy this formula down and now I have the time savings per hour by the different speeds:
Next, what I will do is add different time intervals. I don’t want to strictly look at just a single hour. It will be helpful to set up various different periods. To do this, I’ll create a header for the number of minutes and adjust my formula so that it references the header rather than just multiples by 60:
Now, what I will do is set up more periods and copy the formulas across. Here is what the time savings look like across 15, 30, 45, 60, and 120 minute periods:
To better visualize this, I will create a line chart that shows this data:
The important takeaway from all of this is that for short trips of 30 minutes or less, you aren’t saving even 10 minutes worth of time unless you are speeding excessively (70 mph vs 50 mph), which is not just dangerous but can run you the risk of getting a ticket. But over a few hours of driving, even a 5 mph bump up in speed can save you 12 minutes. It is a safer and more sustainable option to go slower and gradually accumulate time savings.
If you want a quick way to do these calculations without using a spreadsheet, simply calculate how much faster you are going than the speed limit and convert that into a percentage. Then, multiply that by the number of minutes that you are driving for. In the example of going 70 in a 50 zone, you would be 40% over the speed limit. Multiply that by 15 minutes of driving time, and the time saved would be 6 minutes. The formula looks as follows:
I’m not advocating for driving fast and the purpose of this was simply to calculate the theoretical time savings in Excel. If you have other suggestions for problems to solve in Excel, please contact me with your ideas.
If you liked this analysis on how much time speeding saves, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.