Find Max and Min values in one cell

Ev Morris

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!!!

Find more posts tagged with

Accepted answers

Leibel S

@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)), ""))

All comments

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?

Ev Morris

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

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

Ev Morris

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/55721/extract-part-of-a-cells-text

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

https://community.smartsheet.com/discussion/66223/text-to-different-rows-split-by-delimiter

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

Ev Morris

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/66859/pull-text-from-string-using-delimiter

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

https://community.smartsheet.com/discussion/66223/text-to-different-rows-split-by-delimiter

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

Leibel S

@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.

Ev Morris

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.