Highest value on the last six values of data

Nicole Rivera

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

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