I have a recorded drive cycle of a truck which includes speed and coordinates over time. The file looks like this (simplified version)

I want to know the number of times the truck is stopped (when speed = 0) and number them. Therefore, I would like to group the intervals with '0' values (vehicle stopped) in the Speed column and name them in order (Stop 1, Stop 2, etc). Ultimately, my goal would be to somehow be able to calculate the number of stops and duration like this:

Is there any function in Excel which would allow me to do something like that? Thank you.

CodePudding user response：

You can do it using Pivot Tables and a helper column. Also if you have Excel365 with functions like FILTER,UNIQUE and SUMIFS

Formula helper column is just to enumerate properly each group of stops. Notice data need to be sorted properly as your exampel or it won't work:

=IF(D2="STOP";IF(D1="STOP";E1;MAX($E$1:E1) 1);"")

Then pivot table can be inserted:

1. Helper Column and Vehicle Stop into rows section
2. Time-step seconds into values section, formated as Time and operation=Sum
3. Filter field Helper Column to exclude blanks

If you have Excel 365, you can get this output with advanced formulas. In cell J14 formula is:

=UNIQUE(FILTER(D2:D19&E2:E19;D2:D19="STOP"))

And K14 is (and drag down) is:

=SUMIFS($B$2:$B$19;$D$2:$D$19;"STOP";$E$2:$E$19;MID(J14;5;99))

Anyways, I've uploaded the workbook to Gdrive so you can see the Pivot Table and the formulas by yourself. If you don't jave Excel 365, the formulation part may give some errors when you open it: