CODEWITHSUNDEEP
exceltime-seriesduration
alo26
alo26

Reputation: 1

How to identify and name different ranges in one column in Excel?

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

enter image description here

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:

enter image description here

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

Upvotes: 0

Views: 70

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34265

Just as a postscript to this, you could do the same thing without using helper columns by comparing offset ranges to see where the transitions in speed occurred from 0 to 10 (go) or 10 to 0 (stop). The problem is that normally you would have to go outside the data range (a2:a16 and c2:c16) which gets you either a header cell (a1 or c1) or a blank cell (a17 or c17). In the case of time, there is also a special case where the first time-step is taken to be zero.

All this can be avoided in Excel 365 by using vstack to add appropriate dummy values to the beginning or end of the two ranges:

=LET(timeRange,A2:A16,
speedRange,C2:C16,
timeRange1,VSTACK(A2,timeRange),
speedRange1,VSTACK(1,speedRange),
speedRange2,VSTACK(speedRange,1),
startTime,FILTER(timeRange1,(speedRange1>0)*(speedRange2=0)),
endTime,FILTER(timeRange1,(speedRange1=0)*(speedRange2>0)),
stopTime,endTime-startTime,
label,"STOP "&SEQUENCE(ROWS(stopTime)),
HSTACK(label,stopTime))

enter image description here

Upvotes: 0

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11988

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

enter image description here

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:

https://docs.google.com/spreadsheets/d/1t1INxGKJRHFexWnSC5LlIi37JrThjajs/edit?usp=sharing&ouid=114417674018837700466&rtpof=true&sd=true

Upvotes: 1

Related Questions