Excel Help--Finding the Average Duration within the past month

[nini8519]

Hello, 

Trying to find the average duration of a project metric in the past month.

=(AVERAGEIF('Onboarding Interface Tracker'!T6:T285, ">0") AND (COUNTIF('Onboarding Interface Tracker'!S6:S285,">"&DATE(YEAR(TODAY))),MONTH(TODAY)())-1,0))-COUNTIF('Onboarding Interface Tracker'!S6:S285,">"&TODAY()-DAY(TODAY()))

I am pulling the average from another column that already contains a formula.

Would also like to make one for the past year. 

I get an error. 

[#2]

@nini8519 There appear to be a number of issues with that formula including wrong () groupings and trying to AND your AVERAGEIF with other conditions AND trying to subtract 2 COUNTIF statements. Try this (I didn't actually test it but should be close if not right):

=AVERAGEIFS('Onboarding Interface Tracker'!T6:T285,
            'Onboarding Interface Tracker'!T6:T285, ">0",
            'Onboarding Interface Tracker'!S6:S285,">"&EOMONTH(TODAY(),-2),
            'Onboarding Interface Tracker'!S6:S285,"<="&EOMONTH(TODAY(),-1)
            )

  is that >0 criteria required?

As for past year you can use similar format and use EOMONTH(..., -13) for the past 12 months or use that DATE(...) format you were using.  (Using DATE() wasn't a problem except that you had misaligned (), I just switched to EOMONTH because it is cleaner and easier to read)

Hope that helps

[#3]

@mtarler Thank you so much. Yes, that was able to capture what I needed to do!

Much thanks again. 

[#4]

@mtarler I have another part to this that I am getting stumped. 

My final calculation is to obtain a percentage of completed projects in the last month where another column of legacy data is not blank. 

I was able to do the calculation for a timeframe of all time projects and successfully got a percentage. 

=COUNTA('Onboarding Interface Tracker'!AG6:AG285)/COUNTA('Onboarding Interface Tracker'!AI6:AI285)

I counted the number of cells that had dates in the legacy column and divided that by number of cells that had dates in the completed projects column. 

This is where I am getting stuck because I need to add a condition to the completed projects column from the past month. That column is the AI6:AI285 one. 

I tried this, but I am getting 600% as an answer:

=(COUNTA('Onboarding Interface Tracker'!AG6:AG285))/(COUNTIF('Onboarding Interface Tracker'!AI6:AI285,">"&DATE(YEAR(TODAY()),MONTH(TODAY())-1,0))-COUNTIF('Onboarding Interface Tracker'!AI6:AI285,">"&TODAY()-DAY(TODAY())))

[#5]

So you went from dividing all of AG by all of AI to dividing all of AG by only this past month of AI. So why wouldn't you expect the percentage to go high? You haven't shared the sheet/data but if the legacy column has 240 of the 285 rows and completed projects this past month is about 40 that is about 600%. Maybe explain better what you are expecting?