Forum Discussion

nini8519's avatar
nini8519
Copper Contributor
Jul 06, 2022

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

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:S28...
excel
Formulas and Functions
nini8519's avatar
nini8519
Copper Contributor
Jul 07, 2022

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())))

 

 

mtarler's avatar
mtarler
Silver Contributor
Jul 07, 2022
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?

Resources