Max Collect Formula

Options

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!

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!