This spreadsheet was created to meet the required analysis pertaining to Guinness speed records and posted here to allow scrutiny of calculation methods.
To use this spreadsheet, I’m assuming that you are using a GPS antenna and have blackbox logging enabled. As long as the GPS is connected to the flight controller, blackbox automatically records the GPS data. However, the data can only be viewed using the blackbox decoder tool (link below). GPS data does not show up on the blackbox viewer.
To save a lot of tedious work and time. But this depends on how you want to slice your data. Finding the peak speed is simple and takes only a few seconds. However, if you want to go for something official (like a Guinness World Record), analysis is a bit more involved. This spreadsheet makes this analysis a lot easier and is specifically tailored to the speed record requirements for Guinness.
Although one of the links above describe how to interpret GPS log data, this blog has a link to a spreadsheet that can automatically do all the analysis in just a few copy and paste steps.
- GPS Speed Calculator Spreadsheet
- Blackbox Tools
- Sample blackbox file (from XLR 200mph speed run)
- Completed sample spreadsheet
What It Does
The spreadsheet will analyze the data to find consecutive data points that cover a distance of 100m or more but it will only analyze data that is above a certain speed threshold (which you can adjust).
Since speed is always changing, the number of data points required to cover a 100m distance also changes. This means the data must be analyzed using different data sampling sizes. Sample sizes can also be adjusted if needed but the default values (in whole number increments from 4-14) usually work just fine.
When the spreadsheet identifies the first speed run out of the data, it sets this heading as ‘Direction1’ and all subsequent runs fall into this category. All runs in the opposite heading (within a set range) will fall into the ‘Direction 2’ category.
Once complete, the following is shown:
- Fastest pass over a distance of 100m or more for each direction
- For each direction, the distance covered during the pass
- Change in altitude during the pass for each direction
- Angular deviation from horizontal during the pass for each direction
- Heading of each direction
Average speed of the fastest opposite direction passes
Average angular deviation from horizontal for the fastest opposite direction passes
Angle between the headings of the fastest opposite direction passes
Fastest overall 100m or more speed pass
Peak recorded speed
Using the Spreadsheet
Although this spreadsheet saves a ton of time, there are a few very simple steps involved to use it:
- Open the GPS Speed Calculator spreadsheet.
- Download blackbox tools here.
- Unzip the file from above into a folder.
- Place a copy of the blackbox log file into the same folder:
- Click and drag the blackbox log file onto the blackbox_decode.exe file. After a few seconds, 4 files will be generated.
- Open up the file that has the .gps.csv extension:
- Once open, press Ctrl+A to select all the data, then Ctrl+C to copy the data.
- In the Speed Calculator, highlight the cell that says ‘time (us)’ as shown below and press Ctrl+V to paste in the data:
- Next, highlight the light gray rectangle of cells on the right side of the spreadsheet. Press Ctrl+C to copy them:
- Scroll down until you see the first highlighted section of speed run calculations and paste the copied cells below the data as shown:
- Continue scrolling down and pasting the copied cells below the calculated data. Opposite direction runs will be highlighted in alternating colors:
- Once all the calculated data have the analysis cells pasted below them, scroll back to the top left side of the spreadsheet to see the finished analysis:
We Still Need Humans
Depending on what you are trying to accomplish, there are 2 things to verify:
- Were the speed runs in opposing 180° directions within reason?
- Was the total deviation from horizontal relatively level? NOTE: Since gravity is a constant, it only matters that the total horizontal deviation of both passes are within reason. If direction 1 pass is -10° and direction 2 is 10°, any kinetic energy gained in direction 1 is offset by the kinetic energy loss in direction 2.
If there are issues for either one, you can simply delete the bad data so the calculations don’t take it into account.
How It Works
Here is an overview of the calculations done in the spreadsheet and how it analyzes the numbers.
In order to make the spreadsheet a little cleaner, most of the calculation columns are hidden.In order to see them, highlight the column headers, then right click and select unhide from the drop down menu. I’m no expert in Excel so there may be a more efficient way to have Excel do this analysis, but for now, this gets the job done.
After pasting in the GPS data:
- Highest recorded speed is found by finding the max value in column F and displayed in under the Summary.
- Column I finds the time difference between the current data row and the previous row and displays it in seconds.
- Column J finds the altitude above ground by subtracting the first GPS altitude reading (cell E31) from the current data row reading.
- Column K finds the distance travelled between the current data row and the previous row by multiplying the time by the speed.
- Column BD finds the first speed run and displays the average heading for the next 16 data rows. All cells for the rest of this column are filled with the same number to be used as a reference for direction 1. The spreadsheet identifies each speed run in alternating colors between direction 1 and 2.
- Columns L through O repeat 10 times. Each set of 4 columns carry out calculations using the number at the top of the columns (row 44) as the sample size of the GPS data points. Different sample sizes are used since the number of samples needed to cover 100m+ changes with speed. Also, since speed is independent of distance travelled, a wide range of sample sizes are used and analyzed as long as the distance is 100m+.
- Column L: the sum of the distance travelled over x number of cells (x = sample size, and cell range is current row-x+1 rows) is calculated. If the sum is equal to or over 100m, the distance travelled is displayed.
- Column M: if a number is displayed in column L of the same row, column M will calculate the speed over this distance by dividing the distance travelled by the time change over the range of sample points.
- Column N: if a number is displayed in column O of the same row, then the distance travelled for the sample range is shown.
- Column O: if a number is displayed in column M of the same row and is the max velocity in x rows above and x rows below in column M, the altitude change is displayed for the sample range.
After pasting the analysis cells below each speed run:
- In the top row, the max speed for each set of the 4 repeating columns are displayed below their respective columns. For the max speed in this row, the distance travelled and altitude change is displayed in the row below it.
- On the right side of the analysis cells, the max overall speed over 100m distance is displayed along with the corresponding distance travelled and altitude change. The average heading for the run is also calculated. This will be displayed for either direction 1 or 2 depending on the average heading of the speed run compared to the reference heading.
- In the upper left corner of the spreadsheet, the max for the corresponding pass directions are displayed by finding the max value in the corresponding analysis cell columns. Average opposite direction speed is calculated along with the angle of the run relative to the horizontal.