Formula Help - Average IF

Hi everyone. I am hoping you can help me. I am trying to average the time it takes individual case managers to complete a case between specific dates.

The reference cells are {CM Text} in the main sheet and it has to equal the name in [Case Manager]@row in the reference sheet

The average comes from the reference identified as {Days to Complete} in the main sheet

The specific date column is referenced as {GS Action Complete} and between the two dates shown in the [Week End]@row and the [Week Begin]@ row.

I keep getting errors on the below formula.

=AVERAGEIFS({Days to Complete}, {CM Text}, [Case Manager]@row, {GS Action Complete}, <=[Week End]@row, {GS Action Complete}, >=[Week Begin]@row)

Any help would be appreciated in getting this fixed.

Tags:

Best Answers

  • leonastomenio
    leonastomenio ✭✭✭✭
    Answer ✓

    You have to create logic within the formula as AVERAGEIFS is not considered a function in Smartsheet.

    You could collect the range first then do the average:

    =AVG(COLLECT({Days to Complete}, CM Text}, [Case Manager]@row, {GS Action Complete}, <=[Week End]@row, {GS Action Complete}, >=[Week Begin]@row))

    or put @cell

    =AVG(COLLECT({Days to Complete}, CM Text}, [Case Manager]@row, {GS Action Complete}, @cell<=[Week End]@row, {GS Action Complete}, @cell>=[Week Begin]@row))

  • leonastomenio
    leonastomenio ✭✭✭✭
    Answer ✓

    Glad it worked Peppey ;)

Answers

  • leonastomenio
    leonastomenio ✭✭✭✭
    Answer ✓

    You have to create logic within the formula as AVERAGEIFS is not considered a function in Smartsheet.

    You could collect the range first then do the average:

    =AVG(COLLECT({Days to Complete}, CM Text}, [Case Manager]@row, {GS Action Complete}, <=[Week End]@row, {GS Action Complete}, >=[Week Begin]@row))

    or put @cell

    =AVG(COLLECT({Days to Complete}, CM Text}, [Case Manager]@row, {GS Action Complete}, @cell<=[Week End]@row, {GS Action Complete}, @cell>=[Week Begin]@row))

  • Peppey
    Peppey ✭✭

    Leona you are awesome! This worked perfectly thank you so much!

  • leonastomenio
    leonastomenio ✭✭✭✭
    Answer ✓

    Glad it worked Peppey ;)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!