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

Graham Rimell · 05/13/21

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

Leibel S · 05/13/21

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

Paul Newcome · 05/13/21

Try a MAX/COLLECT.

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

{Total Column}, @cell <> ""

says where the total column is not equal to blank.

Graham Rimell · 05/13/21

Thanks, this gets me very close to the result. It gives me the date of the most recent entry, but not the entry itself.

I tried

=MAX(COLLECT(Total:Total, Total:Total, @cell <> ""))

This does give me 10.19, but this is because it happens to be the largest number in the column, not because it's the most recent result in the column.

I suppose I could use the date returned as a look up for an Index/match to find the correct cell, but this is a messy work around!

Leibel S · 05/13/21

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

Graham Rimell · 05/13/21

Yes! That's done it. Thank you.

I got it working initially as two stage formula with =MAX(COLLECT(Total:Total, Total:Total, @cell <> "")) and then an index(match for the date, but your method does it in one.

CaptainPlanet · 09/29/23

https://community.smartsheet.com/discussion/comment/291140#Comment_291140

@Leibel S - Hi, I am working on a formula that you essentially have provided the solution for, however, my issue is my date column is not all unique dates, which as you indicated, is posing a problem. Is there any way to use a similar formula with the same function as the solution you proposed but that will also work with non-unique dates?

My question is posted here:

Pulling data based on most current entry and multiple criteria with other entries on same day? — Smartsheet Community

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

Best Answer

Answers

Help Article Resources

Categories