Hi there, I´m a master´s student who have been stuck with this issue for 10 hours now, and cant proceed with my analyzation without solving this problem... Please help me get over this.
I need help creating an Excel formula that can sum the duration (in time format hh:mm:ss) for each activity level (1, 2, and 3) as they occur in the dataset. The summary calculation should start when the activity level changes (or the dataset begin), and end when the activity level changes again. Each time the level changes, the function should start summing the duration of that row of the same levels anew, discarding the previous summary output before the level changed. The goal is to have a function that can accurately calculate the total time spent on each activity level in the dataset as they occur after each other.
Let me provide an example using a small part of my dataset as attached.
For example, as row 2 to row 13 contain the same activity level (2), but row contain activity level 3. The total duration from row 2-13 needs to be summed in a new column. The same goes with row 14 to row 15, which is of activity level 3, and needs to be summed together as the row below has another activity level, and so on... When in row 16 changes back to level 2, the calculation should start from scratch where the duration time is 00:15:17 as there is only one row of level 2.
I am having trouble understanding how to create this function, and would greatly appreciate any help as all functions I have tried is unsuccessful...
PS: i cannot change the order of the rows as this will take away the whole point
I need help creating an Excel formula that can sum the duration (in time format hh:mm:ss) for each activity level (1, 2, and 3) as they occur in the dataset. The summary calculation should start when the activity level changes (or the dataset begin), and end when the activity level changes again. Each time the level changes, the function should start summing the duration of that row of the same levels anew, discarding the previous summary output before the level changed. The goal is to have a function that can accurately calculate the total time spent on each activity level in the dataset as they occur after each other.
Let me provide an example using a small part of my dataset as attached.
For example, as row 2 to row 13 contain the same activity level (2), but row contain activity level 3. The total duration from row 2-13 needs to be summed in a new column. The same goes with row 14 to row 15, which is of activity level 3, and needs to be summed together as the row below has another activity level, and so on... When in row 16 changes back to level 2, the calculation should start from scratch where the duration time is 00:15:17 as there is only one row of level 2.
I am having trouble understanding how to create this function, and would greatly appreciate any help as all functions I have tried is unsuccessful...
PS: i cannot change the order of the rows as this will take away the whole point
)
