[SOLVED] Journey length (if function?)

**transportplanner** · 06-02-2010, 09:33 AM

Solution; http://www.excelforum.com/excel-gene...ml#post2320765

Hello Internet

I've had a look and have had a play with IF functions, can't get my head around the start of my problem though... thanks in advance for any help, this forum has been excellent in the past.

I've attached an example file, which covers the first hour of data (dummy data in this case but the principle is the same).

We noted the time a vehicle passed at point A, and then again at point B. As such, we should have a rough estimate of journey time (nearest minute).

I know there's a way of doing this, I assume in part using IF, but it's beyond me. I have the point A data in sheet 1 and point B data in sheet 2.

I am looking for a way of quickly putting the time it took each vehicle to get from point A to B (judged by difference in time) in the journey time column in sheet 1.

Also, if there's some particular tutorial which covers this - please let me know.

Thanks in advance.

**JeffCPA** · 06-02-2010, 10:12 AM

So you're tracking each individual vehicle by an ID number the REG column, correct? You can use the VLOOKUP formula here but you need to first cut and paste REG from where it is now to the leftmost column (it needs to become column A) on both Point A and Point B. Then use this on the Point B sheet (if you want the travel time to appear in column E, for instance, paste this in E2):

=ABS(B2-VLOOKUP(A2,'Point A'!$A$2:$B$152,2,FALSE))

Note that this only works if the "lookup range," or the second argument of VLOOKUP, is sorted in ascending order. For that reason, this formula wouldn't work if you pasted it on Point A and referenced Point B data, unless you did some sorting. Since Point A is already in order from QWE1, QWE2,... then it works. But make sure you always have REG sorted in ascending order on Point A.

You should be able to copy this right down. Also, make sure you copy the formatting from "time in" to wherever you're using this formula if you want the result to display as HH:MM.

**transportplanner** · 06-03-2010, 10:47 AM

Ah, thanks. That's pretty fantastic.

However, can the following be taken into account?

1. Make it so that it ignores matching entries where Point B comes before Point A (we were only recording data in one direction, but as some vehicles made multiple journeys it could lead to data in both columns in the wrong order).

2. How can I make it so that when there is a second journey, vlookup goes to the next journey? At the moment, if car ABC makes a journey at 9am and another at 10am, the journey time for the second journey will be 1 hour longer due to looking up the first car ABC entry and time of 9am..

Thanks anyway though, even as is will save me time.

**JeffCPA** · 06-03-2010, 03:53 PM

1. If you want to avoid cases in which Point B is earlier than Point A, use this formula instead:

Please Login or Register  to view this content.

Instead of taking the absolute value of the time difference, you're just taking the difference, and returning 0 if the result would be negative.

2. So you're saying there could be duplicate identifiers in REG if one vehicle made multiple journeys? Do you have anything in the data identifying that it is a second journey? If not it's going to require a little more formula creativity. Can you post some more sample data and include an example of this? If you didn't reuse the ID for a vehicle taking a second journey then this wouldn't be an issue. But there's probably still a way to deal with it even if you do need to repeat IDs at times.

**transportplanner** · 06-04-2010, 07:01 AM

The IDs (registration plates) are repeated in some instances as it is the same vehicle moving past twice.
Probably should have uploaded the actual data in the first place, but had been messing about with the other set to try and work out the solution

There should be a few instances within that hour of repeat vehicles, there will certainly be many when the full 12 hours is input.

Many thanks.