Results 1 to 2 of 2

Thread: Subindexing and Marginal Differencing
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Subindexing and Marginal Differencing

    I have this dataset:
    User Date Time
    A 9/1/2019 8:00 AM
    A 9/1/2019 10:00 AM
    A 9/1/2019 4:00 PM
    A 9/1/2019 5:00 PM
    A 9/2/2019 8:00 AM
    A 9/2/2019 5:00 PM
    B 9/1/2019 9:00 AM
    B 9/1/2019 3:00 PM
    B 9/1/2019 5:00 PM
    The first thing I want to do is add a subindex on User and Date.
    User Date Time User/Date Index
    A 9/1/2019 8:00 AM 1
    A 9/1/2019 10:00 AM 2
    A 9/1/2019 4:00 PM 3
    A 9/1/2019 5:00 PM 4
    A 9/2/2019 8:00 AM 1
    A 9/2/2019 5:00 PM 2
    B 9/1/2019 9:00 AM 1
    B 9/1/2019 3:00 PM 2
    B 9/1/2019 5:00 PM 3
    The second thing I want is a column that takes the difference between times in its row (A) and the row after it (A+1). "n/a" means I don't really care about this value. I just want the interday marginal differences.
    User Date Time User/Date Index Marginal Difference
    A 9/1/2019 8:00 AM 1 02:00:00
    A 9/1/2019 10:00 AM 2 06:00:00
    A 9/1/2019 4:00 PM 3 01:00:00
    A 9/1/2019 5:00 PM 4 n/a
    A 9/2/2019 8:00 AM 1 09:00:00
    A 9/2/2019 5:00 PM 2 n/a
    B 9/1/2019 9:00 AM 1 06:00:00
    B 9/1/2019 3:00 PM 2 02:00:00
    B 9/1/2019 5:00 PM 3 n/a

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,078
    Post Thanks / Like
    Mentioned
    469 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Subindexing and Marginal Differencing

    Hi & welcome to MrExcel.
    How about

    ABCDE
    1UserDateTime
    2A09/01/20198:00 AM102:00:00
    3A09/01/201910:00 AM206:00:00
    4A09/01/20194:00 PM301:00:00
    5A09/01/20195:00 PM4
    6A09/02/20198:00 AM109:00:00
    7A09/02/20195:00 PM2
    8B09/01/20199:00 AM106:00:00
    9B09/01/20193:00 PM202:00:00
    10B09/01/20195:00 PM3

    Data



    Worksheet Formulas
    CellFormula
    D2=COUNTIFS(A$2:A2,A2,B$2:B2,B2)
    E2=IF(D3>D2,C3-C2,"")

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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