AverageIF Formula Help based on Person and Before Certain Date

Hi everyone. I am hoping you can all assist me. I have recently taken over a project and have to fix some Smartsheets. I will try my best to explain the problem. I need to average how long it takes from "Assigned" to "Start". The problem is I have two sheets I need to average "Assigned to Start".

The problem I am having is the first sheet only goes until 05/01/23 so I need that average combined with the average from the second sheet which would be all data from 05/01/23 to 12/31/23.

This is the below formula I have but do not know how to average between two sheets =AVERAGEIF({CY23 eAPP Station - Background Investigat Range 12}, <[Begin Date]54) + AVERAGEIF({CY23 eAPP Station (C) - Background Investi Range 5}, <[Begin Date]54)

Any help would be appreciated.

Find more posts tagged with

Accepted answers

zealvert

Dear @Peppey

You can possibly resolve the issue by combining the Avg(Collect()) functions. Here the Average() function will average only what the Collect() function supplies to it. And You can easily decide what all the Collect() function should supply, and from where / which sheets.

For more info on this please visit the following link.

COLLECT Function | Smartsheet Learning Center

Hope this helps.

Have a great day!

All comments

zealvert

Dear @Peppey

For more info on this please visit the following link.

COLLECT Function | Smartsheet Learning Center

Hope this helps.

Have a great day!

Peppey

Thank you!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.