Search Rows for Dates within a Given Month and Average Corresponding Numbers in Another Column

[Kevin C]

I have a Date Complete (column M) and a Days Overdue (column N). For example, a month (between Oct 1, 2024, and Oct 31, 2024), how do I average the Days Overdue over a range of days for the month? Also November 2024 and December 2024, etc.?

 

[arunsjain]

Hi,
You can use the AVERAGEIFS function in Excel to filter the Days Overdue based on the Date Complete range.

Example formula for October 2024:

 =AVERAGEIFS(N:N, M:M, ">=2024-10-01", M:M, "<=2024-10-31")

For November 2024:

 =AVERAGEIFS(N:N, M:M, ">=2024-11-01", M:M, "<=2024-11-30")

For December 2024:

 =AVERAGEIFS(N:N, M:M, ">=2024-12-01", M:M, "<=2024-12-31")

Dynamic Formula
If you want to make this dynamic (e.g., for any month), you could reference specific cells for the month start and end dates, like:
A1: "Start Date" (e.g., 2024-10-01)
A2: "End Date" (e.g., 2024-10-31)

Then your formula could look like this:

 =AVERAGEIFS(N:N, M:M, ">=" & A1, M:M, "<=" & A2)

This way, you can change the dates in A1 and A2 to calculate the average for any month.

 

[Kevin C]

=AVERAGEIFS(N:N, M:M, ">=2024-10-01", M:M, "<=2024-10-31")

Perfect, it works! I appreciate it. I was attempting to make it much harder than it is.