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

You would need to use
=AVG(COLLECT({APD CI Tracker Range 4}, {APD CI Tracker Range 5}, ="JDI", {Year Column Range}, 2023))

@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())) ?

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

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.
Help Article Resources
Categories
Check out the Formula Handbook template!