Average Collect Issues

Hello! I'm trying to write a formula that will average the amount of time a type of request takes to complete by month. I'm referencing another sheet in this formula and just can't get it to work. Here's what I have so far:

=AVG(COLLECT{Hours to Complete}, ISNUMBER(@cell), {Request Type}, "Quote Request", {Submitted Date}, YEAR(@cell) = 2023, {Submitted Date}, MONTH(@cell) = 1)

I'm not sure if I need the "IS NUMBER" or not, but I don't want it to count blank cells.

Anyone have ideas? Thanks in advance!

Find more posts tagged with

Formulas

Support

Solution Center

functionality

Conditional Formatting

Accepted answers

Devin Lee

The ISNUMBER isn't needed because the AVG function omits blanks.

=IFERROR(AVG(COLLECT([Hours to Complete]:[Hours to Complete], [Request Type]:[Request Type], "Quote Request", [Submitted Date]:[Submitted Date], AND(VALUE(YEAR(@cell)) = 2023, VALUE(MONTH(@cell)) = 3))), "")

=IFERROR(AVG(COLLECT({Hours to Complete}, {Request Type}, "Quote Request", {Submitted Date}, AND(VALUE(YEAR(@cell)) = 2023, VALUE(MONTH(@cell)) = 3))), "")

If you have any missing Submitted Dates there might be an error so make sure they are all filled in.

All comments

Devin Lee

The ISNUMBER isn't needed because the AVG function omits blanks.

=IFERROR(AVG(COLLECT({Hours to Complete}, {Request Type}, "Quote Request", {Submitted Date}, AND(VALUE(YEAR(@cell)) = 2023, VALUE(MONTH(@cell)) = 3))), "")

If you have any missing Submitted Dates there might be an error so make sure they are all filled in.

lugo

This worked! 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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.