0

When multiple numbers are entered in a single cell, is there a way that Excel can detect the largest and smallest numbers and output them into two different cells?

I am creating a dimensional spreadsheet in Excel that lists dimensions and calculates tolerance limits. Yet there is an issue when the given dimension lists two different values. As seen here (dimensional example):

dimensional example

Since this form can be used by multiple individuals, information can be entered in a variety of formats.

What I have currently is the following:

Visual Example

  • B8 for the given dimension (can be entered as 1.257 1.258 1.257,1.258 1.257 - 1.258 1.257(Alt+Enter)1.258 the order of largest and smallest number is not guaranteed.)
  • C8 for lower tolerance
  • D8 for upper tolerance
  • E8 for calculated lower dimension=IF(C8="MIN",B8,IF(C8="","",ROUNDUP(B8-ABS(C8),4))) elaborate formula for =B8-C8 goal is to output minimum number 1.257
  • F8 for calculated upper dimension =IF(D8="MAX",B8,IF(D8="","",ROUNDDOWN(B8+ABS(D8),4))) elaborate formula for =B8+C8 goal is to output maximum number 1.258
Improve this question
1
  • Is VB acceptable? Commented Jun 24, 2021 at 1:05

1 Answer 1

Reset to default
1

Say your double dimension is in A1 you could try:

For maximum:

=FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-"," "),","," "),CHAR(10)," ")," ","</s><s>")&"</s></t>","//s[not(.<//*)][.*0=0]")

For minimum:

=FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-"," "),","," "),CHAR(10)," ")," ","</s><s>")&"</s></t>","//s[.<//*][.*0=0]")
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you very much! I was able to get this running in my formulas.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.