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.
Best Answers

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

Glad it worked Peppey ;)
Answers

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

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

Glad it worked Peppey ;)
Help Article Resources
Categories
Check out the Formula Handbook template!