This spreadsheet was created to meet the required analysis pertaining to Guinness speed records and posted here to allow scrutiny of calculation methods. The spreadsheet can be downloaded here.

Contents:

1. Assumptions
2. The Purpose
3. What It Does
4. Using the Spreadsheet
5. We Still Need Humans
6. How It Works

_

Assumptions

I assume that you have read GPS Data Analysis so you have an understanding of how the formulas in this spreadsheet work. Also, I’m assuming that you are using a GPS receiver, using Betaflight on your flight controller 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 interface.

_

The Purpose

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.

I covered several topics concerning GPS accuracy, found here, the reasons for using GPS logs and how the data is interpreted, both found here.

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.
Downloads:

• GPS Speed Calculator Spreadsheet (The 2nd tab has a completed example)
• Blackbox Tools
• Sample blackbox file (from XLR 200mph speed run)

_

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 in this direction 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 still a few very simple steps involved to use it:

1. Download and open the GPS Speed Calculator spreadsheet.
2. Download blackbox tools here (zip file)
3. Unzip the file from above into a folder.
4. Place a copy of the blackbox log file into the same folder:
5. Click and drag the blackbox log file onto the blackbox_decode.exe file. After a few seconds, 4 files will be generated.
6. Open up the file that has the .gps.csv extension:
   _
7. Once open, press Ctrl+A to select all the data, then Ctrl+C to copy the data.
8. In the Speed Calculator, highlight the cell that says ‘time (us)’ as shown below and press Ctrl+V to paste in the data:
9. Next, highlight the light gray rectangle of cells on the right side of the spreadsheet. Press Ctrl+C to copy them:
10. Scroll down until you see the first highlighted section of speed run calculations and paste the copied cells below the data as shown:
11. Continue scrolling down and pasting the copied cells below the calculated data. Opposite direction runs will be highlighted in alternating colors:
12. 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?

If there are issues for either one, you can simply delete the bad data so the calculations don’t take it into account that particular speed pass.

_

How It Works

To see how the spreadsheet works, download the spreadsheet and follow along in the Sample tab. It helps a lot if you first read through the “Using the Spreadsheet” first.

In order to make the spreadsheet a little cleaner, most of the calculation columns are hidden. To see how the spreadsheet works, we don’t have to unhide all the columns since they repeat themselves for each sample size. However, DON’T DO THIS YET. 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.

As noted in the “What It Does” section, we have these objectives:
A. Find all consecutive sample data that have travelled more than 100m
B. Limit the amount of possible candidates
C. For each “speed pass”, find the fastest 100m+ distance flown
D. For each speed pass, find the change in altitude
E. For each speed pass, find the heading to determine if it is “Direction 1” or “Direction 2”
F. Find the fastest 100m+ distance flown for each pass
E. Calculate the average speed between the 2 opposite direction passes
G. Verify the speed pass headings are 180° from each other (within reason)
H. Verify the speed passes were flown horizontal (within reason)
I. Identify the fastest 100m+ pass
J. Find the fastest recorded speed

There are 6 main sections to the spreadsheet that help us obtain these objectives:
1.Raw data – From the GPS log file. Starting at row 31, these are columns A-G

2. Prep calculations – Starting at row 31, these are columns I-K

• time(s): calculates the time (in seconds) between each data sample
• Surface altitude: converts the above sea level altitude into meters above ground. NOTE: Depending on what units your GPS unit records altitude, you may need to change the formula. My latest GPS recievers record altitude in decimeters. To verify the units, find your local topographic altitude here. Convert from feet to meters and compare it to the GPS data. Either it matches or it will be off by a factor or 10. Adjust the formulas in column J accordingly.
• Distance btwn pts: This is the distance flown between the current sample point and the previous sample point.

3. Sample point sizes – Since we do not know how many sample points will be needed to travel a distance of at least 100m+, we need to start with more or less a guess which is entered in cell M44. The adjacent 10 cells in the same row will increment this sample size by 1 since we will want to make sure we analyze more than just the first consecutive samples that have a distance of 100m or greater.

• To help narrow down the number of consecutive point sizes to calculate, enter a speed (in m/s) into cell AB13. This will act as our flag to determine whether or not to run calculations on a row of data.

4. Speed pass analyzer cells – These are the cells that are copied and pasted under each identified speed run pass (which has already been done in this example). This is used to simply help rearrange values to help process the data. We will revisit this section later.

5. Data processing cells – all the cells in columns L through BK that are below the “sample point size” section (including the hidden ones) do most of the filtering work to find potential candidates for further processing with the “speed pass analyzer cells”. These cells will only show a value if they see data that meets our criteria, so most of these cells remain blank.

To see how these cells work, scroll down to rows 436 through 450. You’ll see that some of the cells in the blue section have values in them, these are average speeds sample sizes that meet the minimum speed requirements (given in cell AB13) and distance requirements (100m+).

For example, if we look at cell W411, it shows a value of 85.63 (m/s). This is how it is calculated:

• Now is a good time to unhide a few columns. Highlight columns R through AB, right click, and click unhide
  • As noted earlier, the “behind the scenes” cells are a little messy, but it works

• If you scroll all the way up, you will see that columns V through Z correspond to a sample size of 6. Scroll back down.
• Before W411 is calculated, cell V411 is calculated. V411 shows a value of 103.09 (m). V411 will only be calculated if F411 (the speed) is greater than our flag speed. If so, cell V411 will add k411 to the preceding 5 cells in column K (giving a smple size of 6). If this is greater than 100m, it will display this value.
• If V411 displays a value, then W411 will divide V411 by the time taken to travel the distance over these 6 sample points.
• Columns X, Y, Z are only clculated for the fastest 100m+ for the current sample size of the highlighted speed pass. In this example, row 412 has the fastest 100m+:
  • Column Z is first calculated and shows the change in altitude for the given sample size
  • Column X is to help organize the data and displays the distance flown which is also shown in column V
  • Column Y calculates the average heading for the given sample size

Revisiting the Speed pass analyzer cells:

• Since row 412 had the fastest 100m+ for sample size 6, it is displayed in the Speed pass analyzer cells. This is done for all sample sizes.
• If a sample size has the fastest 100m+ out of all the other sample sizes of the highlighted speed pass, the values calculated in columns X, Y, Z will also be shown in the Speed pass analyzer cells (normally these are hidden).
• Depending on the direction of the speed pass, the values will be used to fill in the data on the right hand side of the Speed pass analyzer (columns BP through CA). These will be used to compare values of the other speed passes to determine the fastest 100m+

6. Summary – These cells simply determine the best data shown in the Speed pass analyzer cells and run a few more simple calculations so we can determine if the data is acceptable or not.