7 Day Average/30 Day Average

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!

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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. 🙂

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.