Averaging a number column from prior full weeks

I am having a hard time crafting a working formula. I have a Dashboard data sheet that I am pulling/calculating metrics into from other various Sheets.

In one sheet, I have a date column {DID} and a Number Column {NOD}. I am trying to build a formula that will average the number column if the date column is in the last four full weeks. All I have been able to get to work is the last 4 weeks and the current week, where I would need the current week excluded. Add this with the carry over from 25 to 26, I am just getting invalid operation or unparsable errors.

Has anyone attempted a solution like this and been successful? I can build helper columns in the source sheet to call out if the row is in the relevant time frame, but these sheets are connected to our SFDC instance and row/cell limits make me hesitant to add too many helper columns. A single formula or formulas that I could use on the Dashboard Data set would be idea.

Thank you for those who have any suggestions/recommendations!

Find more posts tagged with

Sheets

Formulas

Salesforce Connector

Accepted answers

Paul Newcome

Give something like this a try:

=AVG(COLLECT({NOD}, {DID}, AND(@cell >= TODAY() + (1 - WEEKDAY(TODAY())) - 28, @cell <= TODAY() + (1 - WEEKDAY(TODAY())))))

All comments

Paul Newcome

What exactly do you mean by "full" weeks? Do you mean last four weeks excluding this week, or are you looking for the past 4 weeks Sunday through Saturday stopping at this past Saturday?

l337sc00t4r

Paul,

I would want to start the lookback of the 4 weeks/28 days where the last day would be either this past Sat/Sun. Not picky on considering a week Sun-Sat or Mon-Sun. Is if doing Sun-Sat, the time frame would be Dec 14th - Jan 11th. This would give 4 full weeks of Sun-Sat and would not include this week since we are, as of today, only at Thursday. I essentially want to average the number column going back 28 days the end date is the most recent Saturday that passed. Does that make sense?

Thanks for helping!

Paul Newcome

Give something like this a try:

=AVG(COLLECT({NOD}, {DID}, AND(@cell >= TODAY() + (1 - WEEKDAY(TODAY())) - 28, @cell <= TODAY() + (1 - WEEKDAY(TODAY())))))

l337sc00t4r

Paul,

Thank you for the incredible solution! If you didnt know it already, you are light years ahead of anything ChatGPT could produce.

Paul Newcome

Happy to help. 👍️

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.