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:

Best Answers

  • Alisia Gill
    Alisia Gill ✭✭
    Answer ✓
    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))))

Answers

  • Alisia Gill
    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))))

  • L_123
    L_123 ✭✭✭✭✭✭
    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))

  • Alisia Gill
    Alisia Gill ✭✭
    Answer ✓
    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))))

  • Alisia Gill
    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!