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.


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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!