Average if when date is current year

we want to update the formula to show the average # of problem solving days when the idea is a JDI and the opportunity was submitted in the current year.

Current formula - missing component to reflect only submissions from current year.

=AVERAGEIF({APD CI Tracker Range 5}, ="JDI", {APD CI Tracker Range 4})

{APD CI Tracker Range 5}- potential dropdowns "JDI:", "RCPS", or "Tech"

{APD CI Tracker Range 4} - average # of problem solving days

we also have a helper column for submission year I just am unsure how to incorporate that into my formula

can anyone help? :)

Find more posts tagged with

Formulas

Dashboards

Accepted answers

All comments

Paul Newcome

You would need to use

=AVG(COLLECT({APD CI Tracker Range 4}, {APD CI Tracker Range 5}, ="JDI", {Year Column Range}, 2023))

PaigeKucz

@Paul Newcome - Thank you!! Do you know if I want to avoid having to update it to reflect the current year if i can formulate it to

=AVG(COLLECT({APD CI Tracker Range 4}, {APD CI Tracker Range 5}, ="JDI", {Year Column Range}, @cell = YEAR(TODAY())) ?

PaigeKucz

@Paul Newcome - that formula is giving me a DIVIDE BY ZERO error

Paul Newcome

If you are getting a that particular error then that means either your range 4 does not have any number values in it on the rows that are matching and/or you do not have any rows that are matching.

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.