Merging GPS data with data that does not have lat long information based on 'time' in spreadsheet before creating map

Question

I have GPS data that represents the movement of an animal underwater (so it is really the GPS position of a boat that followed the animal).

I also have a spreadsheet of recorded behaviors from the animal that was carrying a camera, e.g. when they were feeding.

The two data sets were taken at the same time and I can synchronize them so I know what behavior the animal was doing when the boat was in a certain position.

When I merge the two data sets in a spreadsheet I can sort it so that all the data is combined in chronological order. This leaves me with my 'behaviors' mixed in between my 'positional' data but with no lat or long positions themselves.

I would like to assign lat and long data to these empty cells based on the 'closest' time to this row's time that does have lat-long data. This way the recorded behaviors of my animal will be assigned a position in the most accurate way possible. These 'closest' values may be several cells above or below this row.

Can Excel compare the 'timestamp' in this row to the timestamp in the closest cell above or below that includes lat and long data and then calculate which time value is closest to its time value and then use the lat and long data from this row and insert these into its own row?

When I map this in a GIS this may look a bit messy as the GPS data does have some gaps in it. I.e. There might be several behaviors associated with one lat-long location, but this is the best I can do.

Does anyone have any experience of merging positional data with no positional data and assigning positional data based on the timestamp?

An example of this problem is attached. Rows 4 and 5 would be auto-filled with the 'closest time' to them which is the lat and long data from row 3. At the same time row, 6 is closer to the time in row 7 and where therefore be filled with the lat and long data from there. There are hundreds of these gaps throughout my data sets of about 16 animals so I would like a formula to apply to each data set.

Answer 1

I would keep the two datasets separate and use a combination of formulas to connect each time with the nearest GPS. In the example below I separated your table into two datasets, GPS readings and BEHAVIOR readings.

The formula that I placed in cell E10 is,

=INDEX($D$2:$F$5,MATCH(MIN(ABS($D$2:$D$5-D10)),ABS($D$2:$D$5-D10),0),2)

The formula in cell F10 is,

=INDEX($D$2:$F$5,MATCH(MIN(ABS($D$2:$D$5-D10)),ABS($D$2:$D$5-D10),0),3)

Essentially MATCH is used to find the time in the GPS readings that is closest to the BEHAVIOR (time) reading. MIN is the function that finds the smallest time difference. ABS is necessary to remove the difference that negative numbers would cause. MATCH returns the row number, which INDEX uses to then return the cell value at the row and column specified. Latitude is column 3 (in relation to the Array in the formula) and Longitude is column 2.