Max Collect Formula

Ryan P

I am trying to use max collect to display the latest step in a project plan based on the highest value in the "project phase" column that has been marked as green in the "Status" column. I am returning a vlaue of 0 no matter what I try to change here. any thoughts? thanks!

Paul Newcome

The MAX function can only pull numeric values. Because you have the "1.1" in the same cell as text, it is converted to a text value which in turn cannot be pulled. You would need to create a column that houses these numbers separately.

=IFERROR(VALUE(LEFT([Project Phase]@row, FIND(" ", [Project Phase]@row) - 1)), "")

Then you can MAX/COLLECT on this to get the greatest number and INDEX/MATCH on that (if needed) to pull in the appropriate text.

There could be another option, but it would rely on the rows already being in chronological order so that (for example) row 2 will never be "Green" before row 1. That solution would be more simplified, but again requires that the rows already be in chronological order.