Highest value on the last six values of data

Nicole Rivera
Nicole Rivera ✭
in Formulas and Functions

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.

Tags:
· Share on FacebookShare on Twitter

Answers

  • Paul Newcome
    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())))))

    thinkspi.com

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!