Return the lowest cell that isn't blank based on MAX date.

I've got a sheet where we record chemical test results. Some of the tests are done weekly, some every few weeks. I've been using a MAX formula to return the latest result cells based on max date.

However for the tests that aren't done every week, the MAX formula won't work as each new row often is blank.

The 10.19 in the Total column as an example, is there a way to return this value? The most recent non blank cell.

I was thinking about an Index MAX formula, but one that would ignore blank values. Not sure how I'd do this though.


Thanks

Best Answer

  • Leibel S
    Leibel S Community Champion
    Answer βœ“

    @GrahamR

    Are the dates in the date column all unique?

    If yes then you can use the following:

    =INDEX({Total Column}, MATCH(MAX(COLLECT({Date Column}, {Total Column}, @cell <> "")),{Date Column},0))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!