Average time to fill for a given month

Alex Hackford

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.

Jason Duryea

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.

https://help.smartsheet.com/function/collect

Paul Newcome

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

Alex Hackford

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?

Paul Newcome

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