Highest value on the last six values of data

I'm looking to find a formula that will capture the last six months of data and then return the highest value within those six values. On excel, I've used, for example, MAX(OFFSET(B3,COUNTA(B3:B113)-6,0,6,1)) but I see "offset" is not compatible with smartsheet. I don't believe Index and Match would work with what I'm trying to accomplish.

Find more posts tagged with

Sales

Formulas

Support

IT & Operations

Accepted answers

All comments

Paul Newcome

Try something like...

=MAX(COLLECT([Column for Max]:[Column for Max], [Date Column]:[Date Column], AND(@cell <= TODAY(), @cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, DAY(TODAY())), YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, DAY(TODAY())))))

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.