Average time to fill for a given month

Options
edited 10/01/21

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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?

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!