Imagine that we have a series of steps that take a time to run (e.g they could be part of a manufacturing process) such as:

And we now want to calculate the duration of each step. We note that successive steps do NOT finish in chronological order.

For the purpose of this exercise we assume that no two events finish at exactly the same time.

The first thing to do is to determine the order in which the events occur. This can be done by using the RANK function:

The elapsed time will be the difference between the current and previous event finishing. So now we need to have the position of the previous item. However we need to ignore the first item as there is nothing before it:

The Excel function SMALL returns the smallest value from a range:

SMALL(Range, k)

We can use this to determine the time that the previous item finished. However once again, we need to ignore the item that finished first:

Where the formula in Column F is:

=IF(D5=1,0,SMALL($C$4:$C$13,E5))

- We check if it is the earliest finishing step – if so we return 0. Otherwise, we return the time that the previous event finished.

We then take the difference between the times in Column F and C to get the exact process times. However for the first item we will take the time that it finished: