Max,Min, & Avg of multiple numbers in a single cell

[celebrity208]

Here is my excel example data:

Cell A1 = 26,4,12
Cell A2 = 4,4,5
Cell A3 = 0,3,3,3,6,5

I'm trying to get the following results...
for MAX:
B1 = 26
B2 = 5
B3 = 6

for MIN:
C1 = 4
C2 = 4
C3 = 0

for AVERAGE:
D1 = 14
D2 = 4.3333
D3 = 3.3333

the number of integers in each cell is variable

I've tried this solution (http://www.mrexcel.com/forum/excel-questions/5731-multiple-values-within-one-cell.html) but it's crafted for single character integers (or at least integers of the same number of characters).

I've also tried modifying this solution (http://www.mrexcel.com/forum/excel-questions/579461-find-max-value-single-cell-multiple-value.html) but I can't manipulate that one to work either.

I'm trying to do this with a ctrl+shift+enter function as opposed to a VBA. I'm also hesitant to use text to columns because there are a few instances where the number of entries in a single cell are very LARGE.

Thank you, Clay

 

[mole999]

this is simplistic

=B2&","&C2&","&D2

 

[pgc01]

Hi Clay
Welcome to the board

This is the type of problem that is best solved using vba. You'd have a clean and simple solution.

If, however, you really need it to be formula solution, try this array formula in B1:

=MAX(0+MID(A1,FIND("#",SUBSTITUTE(","&A1&",",",","#",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))))),MMULT(FIND("#",SUBSTITUTE(","&A1&",",",","#",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)))+{0,1})),{-1;1})-1))

Same for Min() and Average().

Copy down



<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >D</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >E</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">26,4,12</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">26</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">14</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">4,4,5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">4.333333</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">0,3,3,3,6,5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">6</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">0</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">3.333333</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">1,2,3,4,5,6,7,8,9,55,66,77,888,999,3333,<br>44444,55555</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">55555</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">6203.647</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=6 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table>

 

[celebrity208]

I am astonished at, 1 how quickly people responded and, 2 how quickly you came up with that insane equation! Rock on!

Thank you,

Clay

 

[István Hirsch]

Give this formula a try (enter it with Shift-Ctrl-Enter into B1):

=MAX(1*TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A1)+1-LEN(SUBSTITUTE(A1,",",""))))*99,99)))

Then with MIN() into C1 and with AVERAGE() into D1.

 

[celebrity208]

=MAX(1*TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A1)+1-LEN(SUBSTITUTE(A1,",",""))))*99,99)))

István,
That method is a cleaner equation but for some reason, that I haven't diagnosed yet, it errors on my data. It could be my data (it's dirty with CHAR(10)s and CHAR(13)s and extra spaces and, although I didn't mention it when I opened this thread, it also can have duplicate commas (i.e. Cell A4 = ,7,,,12,,4,5,,1,1).

But, regardless... Thank You also!

Clay

 

[István Hirsch]

Clay, thank you for the feedback.

 

[Aladin Akyurek]

If a generic udf like Charles Williams'sEVAL is admissible...

Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If Not IsEmpty(theInput) Then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(CStr(theInput))
Else
vEval = Application.Evaluate(CStr(theInput))
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function

which you can add to your wb using Alt+F11, you can invoke formulas as shown below:

=MAX(eval("{"&A1&"}"))

=MIN(eval("{"&A1&"}"))

=AVERAGE(eval("{"&A1&"}"))