Average if when date is current year

PaigeKucz

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? :)

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.