Advanced Logic Problem

L

ladygaga

New member
Joined
Oct 15, 2014
Messages
6
Reaction score
0
Points
0
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:
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
 
Sounds like you need a pivot table.
Can you attach a sample file, cut and paste is not formatting
 
ladygaga said:
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
Click to expand...

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
 
Explain your expected results on your file, still confused

Sorry
 
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
 
You must log in or register to reply here.
Back
Top