Find Max and Min values in one cell

Hi All!

I was hoping someone could help me please?

I am looking to find the Max and Min values from a cell containing a range of values separated by ";"

E.g. 0;10;50;10;0

I need the results to show the Min "0", and the Max "10".

Is there any way to do this?

Thanks!!!

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @Ev Morris

    Below is a formula you can use, replace the MAX for MIN to get the lowest number. This will work up till 10 numbers you can add to it if needed.


    =MAX(IFERROR(VALUE(LEFT([Input Values]@row + ";", FIND(";", [Input Values]@row + ";") - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 1)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 2)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 1)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 2)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 3)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 2)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 3)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 4)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 3)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 4)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 5)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 4)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 5)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 6)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 5)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 6)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 7)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 6)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 7)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 8)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 7)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 8)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 9)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 8)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 9)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 10)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 9)) - 1)), ""))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!