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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!