7 Day Average/30 Day Average

Amy Knoll

I am trying to build a formula for a data set that I can calculate as a complete average, but I'd like the 7 day average and the 30 day average.

Here's the formula that works: =AVG([% Successful]:[% Successful])

I know I will need to use AVERAGEIF, but can not get the date parts correct.

Thanks for your help!

Genevieve P.

Hi @Amy Knoll

Since you have a date range (ex. from a certain date to a certain date), we can actually use an AVG(COLLECT formula to specify more than one criteria.

Try something like this:

=AVG(COLLECT([% Successful]:[% Successful], [Date Column]:[Date Column], <= TODAY(), [Date Column]:[Date Column], >= TODAY(-7)))

This looks for a date that's less than or equal to Today, but that is greater than or equal to 7 days ago. For your 30 day average, you can swap out the -7 to be -30.

If you're getting a divide by 0 error, this means that the answer is 0. You can wrap an IFERROR Statement around it to return 0 instead:

=IFERROR(AVG(COLLECT([% Successful]:[% Successful], [Date Column]:[Date Column], <= TODAY(), [Date Column]:[Date Column], >= TODAY(-7))), 0)

Is this the average you were looking for? If you want to specify an exact date instead of TODAY you can use the DATE function, like so:

[Date Column]:[Date Column], <= DATE(yyyy, mm, dd)... etc

Cheers!

Genevieve

Genevieve P.

Hi @Amy Knoll

Since you have a date range (ex. from a certain date to a certain date), we can actually use an AVG(COLLECT formula to specify more than one criteria.

Try something like this:

=AVG(COLLECT([% Successful]:[% Successful], [Date Column]:[Date Column], <= TODAY(), [Date Column]:[Date Column], >= TODAY(-7)))

This looks for a date that's less than or equal to Today, but that is greater than or equal to 7 days ago. For your 30 day average, you can swap out the -7 to be -30.

If you're getting a divide by 0 error, this means that the answer is 0. You can wrap an IFERROR Statement around it to return 0 instead:

=IFERROR(AVG(COLLECT([% Successful]:[% Successful], [Date Column]:[Date Column], <= TODAY(), [Date Column]:[Date Column], >= TODAY(-7))), 0)

Is this the average you were looking for? If you want to specify an exact date instead of TODAY you can use the DATE function, like so:

[Date Column]:[Date Column], <= DATE(yyyy, mm, dd)... etc

Cheers!

Genevieve

Amy Knoll

This worked PERFECTLY. Thanks so much for the help!!

Genevieve P.

I'm so glad! Thanks for letting me know. 🙂