# Average percentage of Assessments in a date range

✭✭✭✭

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)

• Employee

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

October 8 - 10, Seattle, WA | Register now

• Employee

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭

HI @Genevieve P

Your example has worked perfectly thank you.

Kind regards

Dave

• Employee

Hi Dave,

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

Genevieve