Results 1 to 8 of 8

Thread: Advanced Logic Problem

  1. #1

    Advanced Logic Problem



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello gurus, I have a large event dataset for individuals. Below I'm attaching a sample of 10 individuals to give you an idea. Individuals belong to a group and a group consists of one or more individuals. Each individual has one or more event. Each event has one or more episodes within it. Events and episodes have start and end dates. Episodes also have location information. I have to figure out the following: for all individuals that belong to a group that consists of 2 or more individuals, proportion of individual's time spent with any other group member at the same location to the total event duration. In other words, I need to know whether individuals that belong to a group experienced their episodes together (i.e. at the same location on same or overlapping dates) and if yes, the portion of their total event time they spent together. For groups consisting of three or more individuals, I need to figure out the proportion for different combinations of individuals belonging to the same group.
    Can this be done using Excel formulas like nested IFs, AND, OR, COUNTIF, etc? Or would I need to learn VBA to be able to do something like this?
    Last edited by ladygaga; 2014-10-15 at 07:01 PM.

  2. #2
    group_id indiv_id event_id #indiv_per_group dob sex event_type event_starts event_ends(red_if_still_ongoing_when_data_pulled) event_end_reason event_length_in_days event_score episode_location1 episode_location2 episode_location3 episode_location4 episode_location5 episode1_starts episode1_ends episode2_starts episode2_ends episode3_starts episode3_ends episode4_starts episode4_ends episode5_starts episode5_ends
    1 1 1 3 1/1/1980 Female 2 4/26/2012 5/24/2013 X 393 5 Garden X Garden A Garden M Garden H 4/26/2012 5/23/2012 5/23/2012 5/30/2012 6/3/2012 7/11/2012 7/11/2012 5/24/2013
    1 1 2 3 1/1/1980 Female 3 10/21/2013 11/18/2013 Z 28 8 Garden Y 10/21/2013 11/18/2013
    1 2 3 3 1/3/1985 Male 3 3/13/2012 3/11/2013 Y 363 Garden Z Garden B Garden N Garden J Garden P 3/13/2012 3/14/2012 5/2/2012 5/11/2012 8/24/2012 9/1/2012 10/18/2012 10/19/2012 10/29/2012 11/5/2012
    1 3 4 3 1/4/1989 Female 2 3/29/2012 5/13/2013 Z 410 10 Garden Z Garden C Garden N Garden N 3/29/2012 3/30/2012 5/4/2012 3/11/2013 3/11/2013 3/28/2013 3/28/2013 5/13/2013
    2 4 5 2 2/24/1988 Female 1 6/8/2012 1/30/2013 A 236 7 Garden R 6/8/2012 1/30/2013
    2 5 6 2 7/20/1985 Male 1 6/8/2012 2/4/2013 B 241 3 Garden R 6/8/2012 2/4/2013
    3 6 7 1 9/1/1974 Female 3 1/12/2012 1/13/2012 X 1 3 Garden Y 1/12/2012 1/13/2012
    4 7 8 2 4/27/1976 Male 4 8/11/2012 3/4/2013 X 205 4 Garden X Garden J Garden S 8/14/2012 8/24/2012 9/24/2012 10/25/2012 10/25/2012 3/4/2013
    4 8 9 2 4/5/1997 Male 2 10/5/2012 10/19/2012 B 14 2 Garden Y 10/5/2012 10/19/2012
    4 8 18 2 4/5/1997 Male 3 12/6/2012 12/7/2012 B 1 6 Garden Y 12/6/2012 12/7/2012
    4 8 51 2 4/5/1997 Male 4 10/9/2013 10/11/2013 B 2 7 Garden G 10/9/2013 10/11/2013
    18 33 92 2 8/6/1978 Male 1 7/29/2012 3/7/2014 586 8 Garden G Garden C 7/30/2012 8/7/2012 8/7/2012 3/18/2014
    18 34 93 2 11/22/1989 Male 1 7/29/2012 3/7/2014 586 Garden G Garden C 7/30/2012 8/7/2012 8/7/2012 3/18/2014

  3. #3
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Sounds like you need a pivot table.
    Can you attach a sample file, cut and paste is not formatting

  4. #4
    for some reason the attachment function is not working. I put the file on my box.com account. here is the link. Thanks! https://pitt.box.com/advancedlogicsample

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    766
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by ladygaga View Post
    for some reason the attachment function is not working. I put the file on my box.com account. here is the link. Thanks! https://pitt.box.com/advancedlogicsample
    To attach a file, click 'Reply' and a box opens for you to type your post. One of the exit options shouul be 'Go Advanced'. Click this, and look for the paperclip attach option. This will activate 'Manage Attachments', allowing you to locate and upload the file you want to attach. When this is done, click 'Submit Reply'.

    HTH

  6. #6
    Quote Originally Posted by WizzardOfOz View Post
    Sounds like you need a pivot table.
    Can you attach a sample file, cut and paste is not formatting
    here you go
    Attached Files Attached Files

  7. #7
    Explain your expected results on your file, still confused

    Sorry

  8. #8
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    your sample set does not contain any matches, may be best to upload everything for group 1

    Also your location if Event1, Episode 1 is in garden A and episode 5 is also in garden A this does not mean the same episode.

    Rizky, on further analysis its a database query
    group by group_id, event_id, episode_id
    sum on episode time
    identify indiv_id

Posting Permissions

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