AverageIF

Alisia Gill

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

L_123

you only get 1 criteria with the averageif formula

=averageif(range,criteria, optional range to average)

if you want to average based on multiple criteria you should use

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

Alisia Gill

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

L_123

If no results are found it will pop a /0 error. This means that nothing met your criteria. You can choose the message or lack of message you want on this occuring by wrapping the formula in an iferror.

This will say "No Results Found" if nothing matches the criteria instead of the smartsheet side error message

=iferror(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)))),"No Results Found")

This will just be a blank cell

=iferror(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)))),"")

L_123

you only get 1 criteria with the averageif formula

=averageif(range,criteria, optional range to average)

if you want to average based on multiple criteria you should use

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

Alisia Gill

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

L_123

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

Alisia Gill

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

L_123

If no results are found it will pop a /0 error. This means that nothing met your criteria. You can choose the message or lack of message you want on this occuring by wrapping the formula in an iferror.

This will say "No Results Found" if nothing matches the criteria instead of the smartsheet side error message

=iferror(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)))),"No Results Found")

This will just be a blank cell

=iferror(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)))),"")

Alisia Gill

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