How to calculate movement

[Jeanette_W]

I have this spreadsheet (about 25000 rows) too many to do manually.
There are 3 columns - dd/mm/yyyy - hh:mm:ss - Direction
In the direction column there are 2 options BA and AB.. These are traffic counters..

So I would like some help with calculating the time it took for a car to drive over these counters.. When the time is more than 20 seconds, the car went into a yard.
However as you can see from the attached spreadsheet it isn't a simple formula. Well for me anyway.

So can someone help me with calculating the time, bearing in mind the different directions.

Thanks heaps

[NickyC]

Hi
how do you identify a single car, to know which values to subtract?

[Jeanette_W]

with time. from counter to counter is about 20 seconds.. everything over that is another car

[NickyC]

ok ...
if 2 cars in succession go into a yard, how do you tell which one leaves first;
if 2 cars pass the start point at about the same time, how can you tell whether they overtook
if one car leaves the yard at about the same time another is passing, how can you tell which passes the second counter first

or don't these matter much

[Jeanette_W]

You can't overtake at that point.. and yes 2 cars in succession can go into the yard but I am not worried which one leaves first. I'm sure it can be done but I'm kinda pulling my hair out how it can be done

[NickyC]

ok try this
sort your data first on column "direction" then ascending on the time column
this will separate your AB from your BA directions and give you successive observations for each direction

separately for the ABs and BAs

type "start" against the FIRST trip for each direction in column S (10:52:40 for AB and 10:52:33 for BA in your example)

in the cell below (S30 in my working, you may need to tweak) type this formula:

Please Login or Register  to view this content.

copy this formula to the remaining rows for all the ABs and BAs



this says:
if the previous row is not the start of the journey, this row is the start of the journey
if this row is not the start of the journey, and the trip took more than 20 seconds, it went into the yard
if this row is not the start of the journey, and the trip took less than 20 seconds, return the time taken

is this roughly what you want?

[Jeanette_W]

I will have to check with my friend tomorrow but it looks fine to me.
Thanks heaps