-1

I'm trying to create a spreadsheet that will allow me to quickly calculate the amount of time my trains were delayed on a daily basis.

I need a formula that will check for all trains on a particular route after a planned departure time (written in a cell),check these trains actual arrival time and then display the earliest possible time I could have arrived at my destination.

spreadsheet image

For example, in G4 I would like a formula that looks for all trains that depart after 7:49 (A4) and also match both of it's "From" and "To" (C4 & D4). It would then need to check these trains corresponding "actual arrival times" in column F and show the earliest possible train. So for row 4 this would be 9:36.

Any help would be really appreciated as I have been messing around with this for over a day and have gotten nowhere!

A link to the example is here - https://docs.google.com/spreadsheets/d/1eE8t4-_hKB6o5j3W57EHgKzsF9p1usm7nojerjmrDwY/edit#gid=0

Thanks Oli

Oli Kingston
  • 1

1 Answers1

0

Not sure about 9:36 Do you mean 9:39 ?

It's a little difficult to do this but i think what you are looking for is a multiconditional lookup array. I have put below what I think you are trying to achieve.

If A2:A8 is greater than A4, C2:C8 = C4 and D2:D8 = D4, what is the lowest value in F2:F8

Is this correct?

If so then I came up with this formula: =ArrayFormula(MIN(IF((A2:A8>A4),IF((C2:C8=C4),IF((D2:D8=D4),F2:F8)))))

If you get 0.402 or something, format the cell to time. Otherwise, could you break it down for us a bit more?

Glib
  • 242
  • 2
  • 4
  • 16
  • Sorry, I did mean 09:39. That's great, and certainly a big step in the right direction. It seems to be getting tripped up when the best arrival time is on the same row as itself - As in, when getting on the 7:50 gets you there before any other train leaving after that time, it still picks another route. – Oli Kingston Apr 07 '16 at 11:06
  • Wait, fixed that problem. just needed to put ">=" in Thank you so much for your help! – Oli Kingston Apr 07 '16 at 11:10