I have an Excel Long format with the same ID over several days. Participants answered different types of questionnaires and times were recorded. I want to calculate the difference between two types of questionnaires (AE-AB), so that it looks like this:
N Q
day form time difference 1 AB 8:00 8,00 1 AT 8:15 1 AE 16:00 2 AB 8:30 7,30 2 AT 8:33 2 AE 16:00 3 AB 9:00 8,00 3 AT 16:00 3 AE 17:00
There are several "form" types between AB & AE (e.g. AT) that are not of interest. I guess I need to create an "if"statement (if Nx = AE;..) then take the time of that row (IF Nx = AE; Qx - ...) and then find the matching row of AB that was the most recent before AE and match the corresponding time of that row to subtract it)
I'd appreciate any help!