Search for matching data and then calculate the time difference

[critesd]

I have a spreadsheet that lists the time that a device command was sent and the time the response was received. I am trying to calculate the system latency time. The response may occur after more than one command/response has occurred . I need to find the matching response and then calculate the time difference. The time is listed in column A. An example spreadsheet is attached (the real spreadsheet has several 100 rows) and matching data is highlighted. So for example. The response for Row 5 is shown in Row 8. So I need to calculate the time difference (171.352504-171.136878).

[nigelbloomy]

Try this formula in D1 and copy it down.

Please Login or Register  to view this content.

Edit: This is an array formula. You have to enter it by pressing CTRL+SHIFT+ENTER.

[nigelbloomy]

Sorry, I got that wrong. This array formula works.

Please Login or Register  to view this content.

[critesd]

Thank you for your help. My spreadsheet displays an error (#VALUE!) in the first 12 calculations but rows 12 and 13 are correct. This gets me closer!

[critesd]

Thank you nigelbloomy for you help. The following formula seems to work.

=IF(B1="Device",INDEX(A2:$A$13,MATCH(C1,C2:$C$13,0))-A1,"")

[nigelbloomy]

My formula had to be entered as an array, so after you paste it in you have to press CTRL+SHIFT+ENTER instead of just ENTER. That is why you got those #VALUE! errors.

I am glad it led you to think up your own formula though because you did it without needing an array. I like your formula better. Nice job.