Formula Help - Average IF

Peppey

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.

leonastomenio

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

Glad it worked Peppey ;)

leonastomenio

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

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

leonastomenio

Glad it worked Peppey ;)