Averaging Over Date Range

Bryan Letourneau

Hello,

I'm attempting to create a monthly scorecard based on an average completion %. The first picture conveys the completion % for that inspection based on inspection form entries, however I need to average that score over the month.

To avoid an extensive formula, I was able to create a formula to count the first part of the date range which I would then use to divide by the sum of the % complete in that same date range which would then give me an average but I'm unable to complete the <=DATE(2022, 4, 31) portion with out an error.

If someone could provide some assistance in creating either a single long formula to account for averages on a monthly basis, or a way to complete the formula I've started, it would be tremendously helpful.

Any help would be greatly appreciated!

Bryan Letourneau

Hi Jeff,

The formula is referencing the date hidden column as it seemed the easiest way to convert the automated Created column to and easy format for reference.

But that formula worked perfectly, thank you!

Jeff Reisman

@Bryan Letourneau Which of those two date fields is the {Cafe and Breakroom Range 34} range referencing? That may be your issue.

That being said, you can do this with one formula too:

=AVERAGEIF({Date Column Range}, MONTH(@cell) = 4, {% Complete Column Range})

English: Find the rows with dates in the month of April, and give me the average of the % complete column for those rows.

Bryan Letourneau

Hi Jeff,

The formula is referencing the date hidden column as it seemed the easiest way to convert the automated Created column to and easy format for reference.

But that formula worked perfectly, thank you!