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 Shuchat
    Leibel Shuchat ✭✭✭✭✭
    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would need to parse out each of the options into their own cells. How many total options could there be within a single cell?

    thinkspi.com

  • Hi @Paul Newcome,

    The total options can vary but I could guess a max of about 10.

    Can you tell me how to parse out the values please? I've tried some of your past solutions but can't seem to get this to work for me here.

    I've attached a screenshot below of a example data set that I need to assess for Max and Min, I would really appreciate any help!

    Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Which solution(s) have you tried? You are going to need at least 10 helper columns. One for each option.

    thinkspi.com

  • No problem with helper columns, I can just hide these.

    I have tried all of the below and about 100 more! Lol

    https://community.smartsheet.com/discussion/66859/pull-text-from-string-using-delimiter

    The Sheet referenced in the above link seems to work vertically, so doesn't suit my data.

  • No problem adding helper columns, I can just hide these.

    I tried all the below and about 100 more haha

    https://community.smartsheet.com/discussion/55721/extract-part-of-a-cells-text

    The sheet referenced in the above link seems to only work vertically which doesn't suit my data set

  • Leibel Shuchat
    Leibel Shuchat ✭✭✭✭✭
    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)), ""))

  • @Leibel Shuchat this has worked perfect! Thank you so much, this has saved me a lot of work 😀