Match date to project in array and calculate days since last project of same name

I am having trouble with IF and MATCH statements for dates, thinking I might need to use INDEX or VLOOKUP with MIN for this? Basically, I have a worksheet with dates in one columns and i.e. projects in another column. I would like to calculate the average difference in days between matching projects in one column, then the number of days since the last project in another column.

Example:

HTML Code: 

I am not trying to calculate time between Project X and Project Y, I am trying to calculate times between projects of the same matching name.

Dates won't be chronological so it will need to match the project to its date, then find the previous projects and their respective dates, and calculate the average number of days between projects of the same name. (There will be more than 2 projects btw, there will be a lot.) So in the example above, for Project X there are an average 733 days before the next Project X because 05/16/2012-07/07/2011=314 and 07/07/2011-05/11/2008=1152, and the average of those two is 733. Project X's most recent occurrence was 5/16/2012, so that was 340 days ago. The most recent Project Y was actually 09/05/2010 so 09/05/2010-03/05/2010=184, and 03/05/2010-11/07/2005=1579....the average of those is 882 (rounded). The number of days since the last Project Y is 959.

Make sense?

If the average days can't be calculated or would be too difficult then I would be happy to at least calculate days since most recent matching project.

Hi and welcome to the forum :0

Im still working on the average, for the most recent, use this...

Assuming your data is in A1:B7 (I know you have more, so adjust ranges as needed) make up a table somewhere with all your projects in it (I used F2:G3)

Enter this array formula in G2 (with Project X in F2)...

=TODAY()-MAX(IF($B$2:$B$7=F2,$A$2:$A$7,0))
Array formulas must be entered using CTRL SHIFT enter, not just enter - if done correctly, the formula will be wrapped in {}
You can copy this down for all projects

For the average, based on the same ranges, use this array formula, copied down...

=(MAX(IF($B$2:$B$7=F2,$A$2:$A$7,0))-MIN(IF($B$2:$B$7=F2,$A$2:$A$7,"x")))/(COUNTIF($B$2:$B$7,F2)-1)

Hi,

Edit: Wait, think I figured it out. Just change F2 to B2?

Okay, that worked! Thank you so much!

One more question, if there is not more than one project in the Average column it will return "#DIV/0!".....is there any way to have it return "None" or something else instead?

Edit: Nvm figured that out too using IFERROR()

Thanks again, I'm glad to be on the forum

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.