Count of gap between cells
Hi,
I have had a look through but couldn't find the answer to my problem.
I have a table that has the time a car drives along a road in each direction, I have got this into one column, so that you can analyse the gap between cars.
I have attached an image.
https://drive.google.com/open?id=1Nx2ScM_eNhyAFbzwfIVBnnbyMVfFFBje
At each point in the table it says "Car", I want the total of "N" above it until the previous Car, thus showing me the total gap in seconds;
Thank you very much for an help.
Joe
In F2
=IF(E2="CAR",ROWS(E$2:E2)-SUM(F$1:F1)-COUNTIF(E$2:E2,"CAR"),"")
Are these the values which should be returned ?
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]A
[/th][th]B
[/th][th]C
[/th][th]D
[/th][th]E
[/th][th]F
[/th][th]G
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]1
[/td][td][/td][td]Time[/td][td]N to Sh[/td][td]S to Bl[/td][td][/td][td][/td][td]Formula in F2 copied down[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]2
[/td][td]01
[/td][td]08:00:00
[/td][td]0
[/td][td]0
[/td][td]N[/td][td][/td][td] =IF(E2="CAR",ROWS(E$2:E2)-SUM(F$1:F1)-COUNTIF(E$2:E2,"CAR"),"")[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]3
[/td][td]02
[/td][td]08:00:01
[/td][td]0
[/td][td]0
[/td][td]N[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]4
[/td][td]02
[/td][td]08:00:02
[/td][td]N to Sh[/td][td]0
[/td][td]CAR[/td][td]2
[/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]5
[/td][td]02
[/td][td]08:00:03
[/td][td]0
[/td][td]0
[/td][td]N[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]6
[/td][td]02
[/td][td]08:00:04
[/td][td]0
[/td][td]0
[/td][td]N[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]7
[/td][td]02
[/td][td]08:00:05
[/td][td]0
[/td][td]0
[/td][td]N[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]8
[/td][td]02
[/td][td]08:00:06
[/td][td]0
[/td][td]0
[/td][td]N[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]9
[/td][td]02
[/td][td]08:00:07
[/td][td]0
[/td][td]0
[/td][td]N[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]10
[/td][td]02
[/td][td]08:00:08
[/td][td]N to Sh[/td][td]0
[/td][td]CAR[/td][td]5
[/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]11
[/td][td]02
[/td][td]08:00:09
[/td][td]N to Sh[/td][td]0
[/td][td]CAR[/td][td]0
[/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]12
[/td][td]02
[/td][td]08:00:10
[/td][td]0
[/td][td]0
[/td][td]N[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]13
[/td][td]02
[/td][td]08:00:11
[/td][td]0
[/td][td]0
[/td][td]N[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]14
[/td][td]02
[/td][td]08:00:12
[/td][td]0
[/td][td]0
[/td][td]N[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]15
[/td][td]02
[/td][td]08:00:13
[/td][td]N to Sh[/td][td]0
[/td][td]CAR[/td][td]3
[/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]16
[/td][td]02
[/td][td]08:00:14
[/td][td]0
[/td][td]0
[/td][td]N[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]17
[/td][td]02
[/td][td]08:00:15
[/td][td]0
[/td][td]0
[/td][td]N[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet:
Sheet1[/td][/tr][/table]
That is perfect!! Thank you
