Calculating Average by Month

D Gray

I have a formula I created to create an average based on multiple criteria over a rolling year. The formula pulls from two different sheets. My leaders are asking that I convert this to show the average by each month and only focus on the current year instead of a rolling year. I've created rows to represent each month, and I've been trying several things to modify my formula to look at January of this year but nothing is working. My existing (rolling year) formula is below....any thoughts on how I would convert this?

=IFERROR(AVG(COLLECT({Total Days to Complete (SLA)}, {Complete Date}, ISDATE(@cell), {Complete Date}, MONTH(@cell) >= (MONTH(TODAY()) - 12)), COLLECT({Total Days to Complete (SLA) Archive}, {Complete Date Archive}, ISDATE(@cell), {Complete Date Archive}, MONTH(@cell) >= (MONTH(TODAY()) - 12))), "")

D Gray

UPDATE to my above question....

I have successfully updated my YTD average calculation to.....

=IFERROR(AVG(COLLECT({Total Days to Complete (SLA)}, {Complete Date}, ISDATE(@cell), {Complete Date}, YEAR(@cell) = (YEAR(TODAY()))), COLLECT({Total Days to Complete (SLA) Archive}, {Complete Date Archive}, ISDATE(@cell), {Complete Date Archive}, YEAR(@cell) = (YEAR(TODAY())))), "")

HOWEVER....when I try to add to the formula to look at the month it isn't giving me an error message but it's not calculating anything either. It's giving me the false result of NA....(see below formula)...what am I missing?

=IFERROR(AVG(COLLECT({Total Days to Complete (SLA)}, {Complete Date}, ISDATE(@cell), {Complete Date}, YEAR(@cell) = (YEAR(TODAY())), {Complete Date}, MONTH(@cell) = (MONTH(2))), COLLECT({Total Days to Complete (SLA) Archive}, {Complete Date Archive}, ISDATE(@cell), {Complete Date Archive}, YEAR(@cell) = (YEAR(TODAY())), {Complete Date Archive}, MONTH(@cell) = (MONTH(2)))), "NA")

Kelly Moore

Hey @D Gray

The Month function wraps around a date not a number. You mentioned that created rows to represent each month. To be clear, these need to be represented by Month Number, not Month name. I'll call that column Month Number. Be sure to edit the formula to replace this reference to your actual column name.

=IFERROR(AVG(COLLECT({Total Days to Complete (SLA)}, {Complete Date}, ISDATE(@cell), {Complete Date}, YEAR(@cell) = YEAR(TODAY()), {Complete Date}, MONTH(@cell) = [Month Number]@row), COLLECT({Total Days to Complete (SLA) Archive}, {Complete Date Archive}, ISDATE(@cell), {Complete Date Archive}, YEAR(@cell) = YEAR(TODAY()), {Complete Date Archive}, MONTH(@cell) = [Month Number]@row), "NA")

Will this work for you?

Kelly

D Gray

Thank you. I did figure it out and the number was what was hanging me up. Here is what my final formula ended up being.

=IFERROR(AVG(COLLECT({Total Days to Complete (SLA)}, {Contract Type}, HAS(@cell, "Lease"), {Status}, HAS(@cell, "Complete"), {Complete Date}, ISDATE(@cell), {Complete Date}, YEAR(@cell) = (YEAR(TODAY())), {Complete Date}, MONTH(@cell) = 1, {Initial Draft Date}, ISDATE(@cell), {Initial Draft Date}, YEAR(@cell) = (YEAR(TODAY()))), COLLECT({Total Days to Complete (SLA) Archive}, {Contract Type Archive}, HAS(@cell, "Lease"), {Status Archive}, HAS(@cell, "Complete"), {Complete Date Archive}, ISDATE(@cell), {Complete Date Archive}, YEAR(@cell) = (YEAR(TODAY())), {Complete Date Archive}, MONTH(@cell) = 1, {Initial Draft Date Archive}, ISDATE(@cell), {Initial Draft Date Archive}, YEAR(@cell) = (YEAR(TODAY())))), "NA")

Kelly Moore

Glad you got it to work