Not a bad month. Nine DVDs, one was damaged. Average return time was 7.27 hours; longest time was 10.68 hours, quickest was 5.28.
I also have a new chart, the ten dvd moving average. This chart shows the ten dvd moving average of the amount of time it takes to send the next disc to me. By averaging out the time over the most recent ten discs, that would minimize the amount of damage a single delayed shipment would have on the chart. This shows that early 2005, late 2005, and early 2006 I was either being throttled, or Netflix was having problems in their shipping centers. Since February 2006, things are looking ok.
Someone asked me how I calculate these numbers. I have a spreadsheet that I enter in the times from the emails I receive. Netflix sends out an email when they receive a DVD, and when they mail one. Yes, Netflix could game the system, but hopefully they won’t do that.
There are three columns of data I enter:
- Column 1: Time Netflix Receives a DVD
- Entered in military time. This is almost always before noon. There have only been 15 discs received after 10am, of the 326 I’ve sent back.
- Column 2: Time Netflix Mails a DVD
- Entered in military time.
- Column 3: Extra Day
- zero or one. If the next DVD is mailed the same day one was received, enter a zero (or simply leave blank). If the next disc is mailed the next day, enter a one. I have only once had a disc get returned on Friday, and have the next disc mailed on Monday, after the weekend. I counted that as a one, but I could count it as a two, since there was a delay of two mailing days. Twice I have had the disc mailed the next day, but earlier than the time it was received. So for those I enter a zero, but the time ends up as around 20 hours.
There are six columns that the spreadsheet calculates:
- Column 4: Subtract Column 1 from Column 2
- This will tell you how many hours and minutes have elapsed. I format this as a time, not as a number.
- Column 5: Hours
- I extract the hours from Column 4. The Excel formula is =HOUR(Column4)
- Column 6: Minutes
- I extract the minutes from Column 4. The Excel formula is =MINUTE(Column4)
- Column 7: Total Hours
- Add in the extra hours for any shipments that were done the next day. The Excel formula is =Column5 + (24 * Column3)
- Column 8: Decimal portion of an hour
- What fraction of an hour was the minutes? 30 minutes = 50%. The Excel formula is =(Column6 / 60). I format this to two decimal places.
- Column 9: Total time
- How long was between Netflix receiving a disc, and mailing the next? Simply add Column 7 and 8.
At the end of the month, I simply use these three formulas to get the data that I chart.
- Column 10: Average time
- =AVERAGE(Column9 range for the month)
- Column 11: Maximum time
- =MAX(Column9 range for the month)
- Column 12: Minimum time
- =MIN(Column9 range for the month)
Does all that makes sense? Just ask if I’ve muddled the waters more than cleared things up.