I have to transform data from a 1 week survey to usable data.
Based on the 'personID', 'day of the week', 'Tottime' and 'Activity' I have to calculate for each person how many time he spends on the different types of activities per day. The example in the attachment (reply)will make things more clear. Everything has got to be done by functions because of the size of the real survey.
I hope anyone can help me with this problem!
Thx in advance!
Last edited by jaZZerkill; 03-26-2012 at 01:08 PM.
I tried to open the attachment. I get the following error
Invalid Attachment specified. If you followed a valid link, please notify the administrator
Should be solved now?!Originally Posted by Dennis7849
Hi jaZZerkill,
See the attached file and look into the columns N to Z... and let me know if this helps. thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), dilipandey@gmail.com
Looks good although it seems that whenever a person does two times (or more) the same activity a day, the calculation doesn't work any more (marked in red in file in attachment). Also from person C the outcome is not right anymore.
Is it also possible to form column 'L' out of column 'A', because in the real survey 'PersoonId' is a more complicated combinations of letters and numbers.
Last edited by jaZZerkill; 03-26-2012 at 05:43 PM.
Hi jaZZerkill,
See the attachment.
Using formula I have got Q9 as 20 and the logic is :-
Person ID (col K) should be B
Day of the week (col D) should be Monday
Activity (col J) should be 16
considering the above criterias.. the first matching row in the data is row#10 (highlighted) in yellow and hence the result (Tot Time) is 20 which is correctly calculated.
Now... if you need the result as 60 (cell I10 + I11) i.e, the sum of Tot Time of two matching rows in this case (i.e., row#10 and row#11), confirm back ... thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
Last edited by dilipandey; 03-27-2012 at 12:48 AM. Reason: attachment missed, now uploaded
Indeed, because I need the total time for every person(A,B,..) he spends daily on an activity type(14-26); the result in Q9 should be 60 and the result in Y9 should be 30.
Thanks so far for the effort!
Ok... thanks for the confirmation jaZZerkill.
will look into it and provide update by day end today. Thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
Hi,
May be a solution with pivot table
First transform your data in table : when adding data table become dynamic automaticaly
When adding data, don't forget to refresh the pivot table
See attached file
Hope this helps
Best regards
Hi jaZZerkill,
See the attachment which is not getting the desired value 60 as per my post#6, which you also confirmed later. thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
A pivot table could indeed be a way to solve this.
Is it possible to add empty rows in the pivot table? What I mean is the following:
When I transform my data into a pivot table I only get the weekdays for every person when he actually does an activity. Can excel automatic add all weekdays an just fill in zero's if that person doesn't perform an activity that day?
example: see attachment
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks