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.

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.