+ Reply to Thread
Results 1 to 10 of 10

Formula to find time between two different values

  1. 07-09-2012, 10:19 PM #1
    farli078
    farli078 is offline
    Registered User
    Join Date
    07-09-2012
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    5

    Formula to find time between two different values

    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
    Last edited by farli078; 07-09-2012 at 11:23 PM.
    Reply With QuoteRegister To Reply
  2. 07-09-2012, 10:42 PM #2
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Formula to find time between two different values

    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
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir
    Reply With QuoteRegister To Reply
  3. 07-09-2012, 11:11 PM #3
    farli078
    farli078 is offline
    Registered User
    Join Date
    07-09-2012
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Formula to find time between two different values

    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
    Reply With QuoteRegister To Reply
  4. 07-10-2012, 05:11 PM #4
    icestationzbra
    icestationzbra is offline
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: Formula to find time between two different values

    is this what you are looking for?
    Attached Files Attached Files
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -
    Reply With QuoteRegister To Reply
  5. 07-10-2012, 05:38 PM #5
    farli078
    farli078 is offline
    Registered User
    Join Date
    07-09-2012
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Formula to find time between two different values

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

    Much appreciated
    Reply With QuoteRegister To Reply
  6. 07-10-2012, 05:59 PM #6
    icestationzbra
    icestationzbra is offline
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: Formula to find time between two different values

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

    you are welcome.
    Reply With QuoteRegister To Reply
  7. 07-10-2012, 09:54 PM #7
    jason.b75
    jason.b75 is offline
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Formula to find time between two different values

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

    F2
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    G2 ** this is an array formula and must be confirmed with Shift Ctrl Enter **
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    H2 ** if editing, note that the first lookup range excludes the last row in the table, the second range excludes the first row **
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    I2
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 07-10-2012, 10:26 PM #8
    farli078
    farli078 is offline
    Registered User
    Join Date
    07-09-2012
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Formula to find time between two different values

    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.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 07-11-2012, 06:57 AM #9
    jason.b75
    jason.b75 is offline
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Formula to find time between two different values

    See if this helps

    Online%20Help(1).xlsx
    Reply With QuoteRegister To Reply
  10. 07-11-2012, 04:53 PM #10
    farli078
    farli078 is offline
    Registered User
    Join Date
    07-09-2012
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Formula to find time between two different values

    Primo! That's it exactly. Ahh got to love helpful forums. Thanks a bunch aye.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1