Q-talk 152 - Q Crash Excel Spreadsheet
- Category: Q-Talk Articles
- Published: Saturday, 28 April 2012 10:10
- Written by Mike Evans
- Hits: 4470
by Mike Evans
[EDITOR'S NOTE: Mike Evans created an accident database in Microsoft Excel that should help the curious cull very interesting information about our little Q's. HE was also gracious enough to write a nice article to describe some of the "filtering" functions in Excel so that you can find just the information you might be looking for. Mike and I hope that you will take a look at the information and help fill in some of the blanks. To keep a little better control of the information and ensure its accuracy, Mike has volunteered to be the point of contact for any needed changes to the spreadsheet. You will need Microsoft Excel 2007 or higher to open the file.]
Attached below is my MS Excel “crash” project. I’m really quite pleased with the results and am hopeful that the folks will
1- Add information to help fill in some of the blanks (ie engine type) and
2- Use it to learn from so they don’t make some of these mistakes.
First of all, a little disclaimer. For the most part, if I use the term “Pilot error”, it was usually because the NTSB also specifically specified so. There may be a few instances where I took a little poetic license. I am prepared to be corrected or to add more explanation from an offended party. Regarding this same column, in order to make the spreadsheet more “searchable”, I generalized categories or problems. Again, this process may have led to some over-generalizations. However, the NTSB report is there (un-retouched) to add clarity.
There were many instances of multiple crashes for one N-Number. Initially, I allowed all of these accidents to be in one row. Then I realized that was not a good idea, so I split them all out, but kept them in adjacent rows. Of course this data could be sorted any way anyone desires.
Many readers may not be familiar with some of the features in Excel that I have used. I’m particularly thinking of the filter for each column. As an example, here are some statistics I gleamed from this data with the use of filters. First, I selected column AH (Fault).
In order to set this filter to all “Pilot errors”, I had to select all of the Pilot error rows from the column AH filter. It looks like this:
In case this is not obvious, this data says that 113 out of 194 accidents were caused by Pilot error – that’s 58%. Frankly, from recent information I have heard, that is probably being generous.
I carried on with filters on other columns. For example, when I added a filter to the “Q2” from column Q, we see a total of 69 accidents for Pilot error in Q2 aircraft. Filtering on column K for non-fatal accidents, we have a total of 55 accidents. When I changed the column “K” filter to fatal (1) and Fatal(4), I see that pilot errors for Q2 drivers resulted in 10 deaths.
It is also interesting to compare the accident rate with phase of flight. I would like to get more data on engine type. Also, the reports are not always clear as to aircraft type.
When using these filters, be sure to note the icon – I outlined the column filter icons with a red circle – you can see at a glance which columns have filters set.
Lastly, when you “mouse over” any of the rows in column AE (Report Status), you will see a comment that holds the NTSB probable cause.
Perhaps I could have made the assumption that all of the Q2 aircraft are powered by Revmasters and all Q200s are powered by O-200, but I was afraid that I might be introducing bad data, so I invite those owners to email me with this and any other details that would increase the value of this work.
I wanted to “Lock” this spreadsheet in order to control the data, but I find I can’t without also losing the ability to use the filters.