# AverageIF

Options

I am trying to get the average number of days to fill a position, the formula I am using is pulling data from my worksheet for the Position type, date range, and days to fill. With my formula below I am getting an Incorrect Argument. I think it has to do with the bolded portion of the formula.

=AVERAGEIF({Time to Fill - Position Title}, "Front Office", {Time to Fill - Time to Fill}, {Time to Fill - Date Accepted}, AND(@cell >= DATE(2020, 7, 1), @cell <= DATE(2020, 7, 31)))

Tags:

Options

Perfect, that worked on all the position types, except for one. I received an error #Divide by zero?

=AVG(COLLECT({Time to Fill - Time to Fill}, {Time to Fill - Position Title}, "Medical Assistant", {Time to Fill - Date Accepted}, AND(@cell >= DATE(2020, 7, 1), @cell <= DATE(2020, 7, 31))))

• Options

I tried this and I am still getting the argument. I am pulling data from multiple columns on my smartsheet.

=AVG(COLLECT({Time to Fill - Position Title}, "Front Office", {Time to Fill - Time to Fill}, {Time to Fill - Date Accepted}, AND(@cell >= DATE(2020, 7, 1), @cell <= DATE(2020, 7, 31))))

• ✭✭✭✭✭✭
Options

=AVG(COLLECT( {Time to Fill - Time to Fill}, {Time to Fill - Position Title}, "Front Office", {Time to Fill - Date Accepted}, AND(@cell >= DATE(2020, 7, 1), @cell <= DATE(2020, 7, 31

The first 2 parts of a collect are ranges. The first value is the range you want to average, the second is the range you want to check for criteria.

=avg(collect(range to average,criteria range 1, criteria 1, criteria range 2, criteria 2))

Options

Perfect, that worked on all the position types, except for one. I received an error #Divide by zero?

=AVG(COLLECT({Time to Fill - Time to Fill}, {Time to Fill - Position Title}, "Medical Assistant", {Time to Fill - Date Accepted}, AND(@cell >= DATE(2020, 7, 1), @cell <= DATE(2020, 7, 31))))

• Options

Perfect, thank you for your help. Everything is working great now.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!