Need to average a range within a certain month

Elguapo24

I am trying to average a column that has its own set criteria and calculating it with in a month. I am separately doing these two formulas but now want to combine it. My first formula:.

=IFERROR(AVG(COLLECT({time in days}, {hospital specialty}, "Acute Care", {job type}, <>"PCT")), "")

This gives me an average time to nursing practice for my entire data set. I want to be able to average it by month that the data was entered.

I was already counting the entries by month with this formula:

=COUNTIFS({date}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2020))

I tried to combine the two and can't get it right. Please help!

Paul Newcome

Try this:

=IFERROR(AVG(COLLECT({time in days}, {hospital specialty}, "Acute Care", {job type}, <>"PCT", {date}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2020))), "")

Paul Newcome

Try this:

=IFERROR(AVG(COLLECT({time in days}, {hospital specialty}, "Acute Care", {job type}, <>"PCT", {date}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2020))), "")

Elguapo24

Thanks Paul! That worked perfect. You are awesome.

Paul Newcome

Happy to help. 👍️