Formula to find time between two different values

[farli078]

Hello,

I have two columns of data, one is made up of A's and AT's (corresponding to events (A) and stimuli (AT)), and the other is the time at which those events occurred. An example is below. I am simply wanting to find the time between a stimulus (red, AT) and the following event (blue, A). I have 50 AT values every 5 seconds but the number of events (A) that occur between each stimulus varies. I could do this manually but as there are thousands of simple calculations to do I'd rather not ha ha.



A 1
A 1.2
A 1.5
A 2.7
A 2.9
AT 3
A 3.6
A 4.2
A 4.5
A 5
A 5.3
AT 8
A 8.8
A 9.2


I hope that made sense.

Thank you

[vlady]

maybe you work this out...

=COUNTIFS(B1:B14,">=3",B1:B14,"<=8",A1:A14,"=A")

the bold ones could be cell references..
=COUNTIFS(B1:B14,D3,B1:B14,"<=8",A1:A14,D4)

D3 contains At time 3
D4 contains At time 8

[farli078]

Thanks vlady for the quick response. I gave that a go but I'm not sure it's what I'm after sorry. I could just be doing it wrong of course. Those numbers I put in are just random numbers to give an idea of the layout. I'm wanting the simple equation =Bx - B(x-1), where x is the time of the event following the stimulus, and x-1 is the time of the stimulus. The difficulty arises in setting a criteria so that excel knows to only do that calculation for every AT value and the following A value

But thank you again for you help, it is much appreciated

[icestationzbra]

is this what you are looking for?

[farli078]

Holy crap! Thank you, that is exactly what I'm after, now to just try replicate what you did.

Much appreciated

[icestationzbra]

didn't realise you were after holy crap, i could have given you some of that too ...

you are welcome.

[jason.b75]

Some alternative formula using the workbook posted by icestationzbra in post #4,

F2

=IF(ROWS(F$2:F2)>COUNTIF(A:A,"AT"),"-","AT")

G2 ** this is an array formula and must be confirmed with Shift Ctrl Enter **

=IF(F2="AT",SMALL(IF($A$2:$A$22="AT",$B$2:$B$22),ROWS(G$2:G2)),"-")  

H2 ** if editing, note that the first lookup range excludes the last row in the table, the second range excludes the first row **

=IF(N(G2),LOOKUP(G2,$B$2:$B$21,$B$3:$B$22),"-")  

I2

 =IF(N(H2),H2-G2,"-")

[farli078]

Man this site is awesome, but I plead to be an excel noob sorry. I've tried just copying and pasting both sets of formulas in and changing the range etc. to match my actual data. No luck so far, on the account of excel noobness. I've attached 3 examples of my actual data that I want to analyse if you're willing to give it a crack? My mate actually gave me a simple equation that works it out, but doesn't put it all in nice ordered rows haha. Got to have it pretty looking of course.

[jason.b75]

See if this helps

Online%20Help(1).xlsx

[farli078]

Primo! That's it exactly. Ahh got to love helpful forums. Thanks a bunch aye.