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
-
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
-
Try a MAX/COLLECT.
=MAX(COLLECT({Date Column}, {Total Column}, @cell <> ""))
{Total Column}, @cell <> ""
says where the total column is not equal to blank.
-
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!
-
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))
-
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.
-
@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:
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!