Average time to fill for a given month

Hi,
I'm looking to calculate the average TTF (time to fill) for a given month and was attempting to use a combination of AVGIF/AND to do so. The formula I have is : =AVERAGEIF({Hiring Plan Range 1}, AND({Hiring Plan Range 2} >= DATE(2021, 1, 1), {Hiring Plan Range 2} < DATE(2021, 1, 31)){Hiring Plan Range 1})
Hiring Plan Range 1 = Time to fill column
Hiring Plan Range 2 = Offer acceptance date column
I'd like to show this over time for the year so I'm looking to calculate each month individually. Basically I'd like to average the TTF value when they fall between the start/end of the given month in the Offer Acceptance Date column.
Any suggestions on how I can improve my formula above? I'm getting #Unparsable as is.
Answers
-
The last {Hiring Plan Range 1} is out of place. AVERAGEIF only allows for one range and one condition. I believe the AND() function can be used as you have it.
=AVERAGEIF({Hiring Plan Range 1}, AND({Hiring Plan Range 2} >= DATE(2021, 1, 1), {Hiring Plan Range 2} < DATE(2021, 1, 31)){Hiring Plan Range 1})
You might also try using AVG(COLLECT(...)) instead, which might help with handling the multiple criteria better.
-
Your syntax is a little off. Try this instead:
=AVERAGEIF({Hiring Plan Range 2}, AND(@cell >= DATE(2021, 1, 1), @cell < DATE(2021, 1, 31), {Hiring Plan Range 1})
-
Thanks for the feedback. I've tried both suggestions. Jason's is returning unparsable and Paul's is returning 0 for all months even though there's data there.
February for example, looks like this on our hiring plan:
However, I'm getting 0 when the average of these numbers should be 60.5
Any suggestions?
-
I missed a closing parenthesis. Sorry about that. Try this...
=AVERAGEIF({Hiring Plan Range 2}, AND(@cell >= DATE(2021, 1, 1), @cell < DATE(2021, 1, 31)), {Hiring Plan Range 1})
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 430 Global Discussions
- 150 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 500 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!