AVGIF Formula Based on Date Range

Hello everyone,

I'm trying to get a average of a column if the cells in another column are within 90 days of today.

My current formula is =AVERAGEIF([Pick-up Variance]:[Pick-up Variance], [Pick-up Date]@row>TODAY(-90)).

I tried using AVG(Collect) as well with no luck.

=AVG(COLLECT([Pick-up Variance]:[Pick-up Variance], [Pick-up Date]:[Pick-up Date], , but I'm getting a Invalid Operation.'

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

@eLIZo That error message indicates an issue with the operator for the TODAY value. Let's try this, it seems to work in my test sheet:

=AVERAGEIF([Pick-up Date]:[Pick-up Date], @cell >TODAY(-90), [Pick-up Variance]:[Pick-up Variance])

All comments

Jeff Reisman

@eLIZo The syntax for the AVERAGEIF function is:

AVERAGEIF(range, criterion, [range to average])

So your formula should be something like:

=AVERAGEIF([Pick-up Date]:[Pick-up Date], >TODAY(-90), [Pick-up Variance]:[Pick-up Variance])

"Give me the average of the Pick-up Variance values in all the rows where the Pick-up Date is within the past 90 days."

eLIZo

https://community.smartsheet.com/discussion/comment/362427#Comment_362427

Yeah, I created the formula that way first and received a INVALID OPERATION. Tried it again and same thing

Jeff Reisman

@eLIZo That error message indicates an issue with the operator for the TODAY value. Let's try this, it seems to work in my test sheet:

=AVERAGEIF([Pick-up Date]:[Pick-up Date], @cell >TODAY(-90), [Pick-up Variance]:[Pick-up Variance])

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.