2

I am trying to set some data up for further analysis. I have 3 columns in my data set. The first column indicates a project, the second column indicates a task within that project, and the third column indicates the date that task was completed.

What I like to analyze is, for each project, how long it took to get from task number #1 to each other given task within that same project. What I mean by this is the fourth column will always say that task #1 to task #1 was zero days difference, task #1 to task #2 is XXX days difference, Task #1 to task #3 is XXX days difference, and so on so forth, but have this days difference counter only count the difference if the tasks are in the same project. If they are in different projects they should reset.

The tasks are not labeled numerically, and different projects have different tasks, so the only way to know what the first task is is by the minimum date within a project, and the next task will be the closest date to the first task and so forth.

Here is an example spreadsheet of my data setup for the problem I am trying to solve

If you have a better way of organizing a data set like this please feel free to chime in.

Pat Myron
  • 129
Keith Miller
  • 27
  • 3

1 Answers1

2

You can use an array formula to get what you want. For this sample data:

enter image description here

Select D2 and paste the following into the formula bar and then press Ctrl+Shift+Enter:

=C2-MIN(IF($A$2:$A$16=A2,$C$2:$C$16,1000000))

Fill the formula down column D. This will show the days since the first task of the same project.

enter image description here

Excellll
  • 12,847