3

enter image description here

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.

1 Answer 1

2

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.

table result

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)

table with formula

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.

3
  • Hi cndnflyr, Thank you for your response and it is definitely on the right track. It has worked for the first row but it has not worked for the subsequent rows. In the bottom 3 rows, they have taken it from the GPS data with the time stamp of 11:57:00, but I need the 12:29:05 (Behav) row to take its data from the 12:30:48 (GPS) row and the 12:26:54 (Behav) row needs to be extracted from the 12:25:51 (GPS) row. They have found the lat-long data for the minimum value in the range I asked instead of the closest time value for each row.
    – RMM
    Commented Aug 7, 2021 at 12:08
  • You are correct. It turns out VLOOKUP was an over complication and I only needed INDEX. I've edited the answer to reflect this.
    – cndnflyr
    Commented Aug 7, 2021 at 16:54
  • 1
    Fantastic. Thank you very much cndnflyr! This has solved a big headache for me. I will post this answer in other forums I have asked this question as this is very useful for people doing similar research to me where they are combining positional and non-positional data based on time of events. All the best to you!
    – RMM
    Commented Aug 9, 2021 at 11:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.