Average percentage of Assessments in a date range

Dave Godfrey

We do progress assessments on a monthly basis and the assessments are given a score as a percentage.

I can write the formula to give the average score for all of the assessments on the sheet but I am struggling to write the formula that allows me to find the average score in a date period.

The formula I have tried is

=AVERAGEIF({MOS Assessment Total %}, {MOS Assessment Date}, >=$[Start Date]$1, {MOS Assessment Date}, <=$[End Date]$1)

Genevieve P.

Hi @Dave Godfrey

When you have multiple criteria to include in your Average formula (such as looking at two different dates), you'll actually want to use an AVG(COLLECT formula instead. The COLLECT function will filter down the initial range to Average based on your multiple criteria.

For example:

=AVG(COLLECT({Range to Average}, {Criteria range 1}, criteria 1, {Criteria range 2}, criteria 2, ...) etc

So in your instance, try something like this:

=AVG(COLLECT({MOS Assessment Total %}, {MOS Assessment Date}, >=$[Start Date]$1, {MOS Assessment Date}, <=$[End Date]$1))

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Dave Godfrey

When you have multiple criteria to include in your Average formula (such as looking at two different dates), you'll actually want to use an AVG(COLLECT formula instead. The COLLECT function will filter down the initial range to Average based on your multiple criteria.

For example:

=AVG(COLLECT({Range to Average}, {Criteria range 1}, criteria 1, {Criteria range 2}, criteria 2, ...) etc

So in your instance, try something like this:

=AVG(COLLECT({MOS Assessment Total %}, {MOS Assessment Date}, >=$[Start Date]$1, {MOS Assessment Date}, <=$[End Date]$1))

Let me know if this works for you!

Cheers,

Genevieve

Dave Godfrey

HI @Genevieve P

Your example has worked perfectly thank you.

Kind regards

Dave

Genevieve P.

Hi Dave,

I'm glad that worked for you! Hope you have a good weekend.

Genevieve