How to calculate the Median from a cell with varying amounts of numbers separated by commas?

[Markige]

Hello

In essence what I need is very simple but I cannot find an answer anywhere online.

I have a long string of numbers of variable length (sales in a week) in a format where another number simply gets added to the string each week. I think I am right in saying I want to be able to find out the Median (To IGNORE peaks and troughs such a 0 sales etc when a product was out of stock).

Please help me do this? It would save me a lot of time using manual judgement each time.


[TABLE="width: 552"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Product[/TD]
[TD]Sales/week[/TD]
[TD]Median sales[/TD]
[/TR]
[TR]
[TD]Bench[/TD]
[TD]0,0,9,0,9,10,13,8,0,0,13[/TD]
[TD] ?[/TD]
[/TR]
[TR]
[TD]Seat[/TD]
[TD]2,3,2,8,2,3,4,5,0,0,0[/TD]
[TD] ?[/TD]
[/TR]
[TR]
[TD]Tennis Racket[/TD]
[TD]22,0,4,9,22,35[/TD]
[TD] ?[/TD]
[/TR]
[TR]
[TD]10L Paint[/TD]
[TD]89,101,165,50[/TD]
[TD] ?[/TD]
[/TR]
</tbody>[/TABLE]



Any help would be majorly appreciated!
Many thanks
Mark

 

[Aladin Akyurek]

Hello

In essence what I need is very simple but I cannot find an answer anywhere online.

I have a long string of numbers of variable length (sales in a week) in a format where another number simply gets added to the string each week. I think I am right in saying I want to be able to find out the Median (To IGNORE peaks and troughs such a 0 sales etc when a product was out of stock).

Please help me do this? It would save me a lot of time using manual judgement each time.


[TABLE="width: 552"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Sales/week[/TD]
[TD]Median sales[/TD]
[/TR]
[TR]
[TD]Bench[/TD]
[TD]0,0,9,0,9,10,13,8,0,0,13[/TD]
[TD] ?[/TD]
[/TR]
[TR]
[TD]Seat[/TD]
[TD]2,3,2,8,2,3,4,5,0,0,0[/TD]
[TD] ?[/TD]
[/TR]
[TR]
[TD]Tennis Racket[/TD]
[TD]22,0,4,9,22,35[/TD]
[TD] ?[/TD]
[/TR]
[TR]
[TD]10L Paint[/TD]
[TD]89,101,165,50[/TD]
[TD] ?[/TD]
[/TR]
</tbody>[/TABLE]



Any help would be majorly appreciated!
Many thanks
Mark

If a generic udf like Charles Williams's EVAL 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:

=MEDIAN(eval("{"&B2&"}"))

and copy down.

 

[Markige]

Hello

Many thanks for your fast reply!!!

Unfortunately I can't understand any of it except the "=MEDIAN(eval("{"&B2&"}"))" part.
Unfortunately because I can't figure out the start, entering this just returns #NAME? as below.

SORRY FOR BEING A NEWBY :D

[TABLE="width: 474"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Product[/TD]
[TD]Sales/week[/TD]
[TD]Median sales[/TD]
[/TR]
[TR]
[TD]Bench[/TD]
[TD]0,0,9,0,9,10,13,8,0,0,13[/TD]
[TD="align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD]Seat[/TD]
[TD]2,3,2,8,2,3,4,5,0,0,0[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Tennis Racket[/TD]
[TD]22,0,4,9,22,35[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]10L Paint[/TD]
[TD]89,101,165,50[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]


Is there any way you can create this simple spreadsheet and upload it as your solution working?

Kind Regards
Mark

 

[Aladin Akyurek]

Hello

Many thanks for your fast reply!!!

Unfortunately I can't understand any of it except the "=MEDIAN(eval("{"&B2&"}"))" part.
Unfortunately because I can't figure out the start, entering this just returns #NAME? as below.

SORRY FOR BEING A NEWBY :D

[TABLE="width: 474"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Sales/week[/TD]
[TD]Median sales[/TD]
[/TR]
[TR]
[TD]Bench[/TD]
[TD]0,0,9,0,9,10,13,8,0,0,13[/TD]
[TD="align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD]Seat[/TD]
[TD]2,3,2,8,2,3,4,5,0,0,0[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Tennis Racket[/TD]
[TD]22,0,4,9,22,35[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]10L Paint[/TD]
[TD]89,101,165,50[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]


Is there any way you can create this simple spreadsheet and upload it as your solution working?

Kind Regards
Mark

You need to add the EVAL code to your workbook, using Alt+F11 before you can use the EVAL function...

 

[Markige]

Hello yes Ok, please could you talk me through doing that?
I hit alt F11 and created a module there then saved it with your text, but that doesn't seem to work?
Do I create a module? If no what do I do where? & what text do I enter there exactly?

Many thanks
Mark

 

[Aladin Akyurek]

Hello yes Ok, please could you talk me through doing that?
I hit alt F11 and created a module there then saved it with your text, but that doesn't seem to work?
Do I create a module? If no what do I do where? & what text do I enter there exactly?

Many thanks
Mark

Open the target book and close any other.

Click on a sheet name (does not matter which) and choose the View Code option...

Select This Workbook on the left pane.

Run the Insert | Module option.

Copy the EVAL code and paste it in the right pane.

Now run File | Close and Return to Microsoft Excel.

Now you are ready to run the MEDIAN formula with EVAL.

 

[Markige]

Hello

I am really sorry for the huge delay in replying, I gave up for a while but I am still struggling with this at work.

1)
I thought I understood your clear guidelines but it still isn't working. This is the URL for the file I have attempted this on - it just fails? See Here

TinyUpload.com - best file hosting solution, with no limits, totaly free

Could somebody check it out, adjust it or whatever is required? Then re-upload for me?

2) Once that works, is it simple to say, just assess the first sat 8 numbers from the left? Or something like that?

3) Can it also ignore 0's Maybe?

Kind Regards
Mark

 

[Aladin Akyurek]

Why do you have a colon (i.e., in those cells instead of a comma as separator?

 

[Rick Rothstein]

I have a long string of numbers of variable length (sales in a week) in a format where another number simply gets added to the string each week. I think I am right in saying I want to be able to find out the Median (To IGNORE peaks and troughs such a 0 sales etc when a product was out of stock).

Please help me do this? It would save me a lot of time using manual judgement each time.

[TABLE="width: 552"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Sales/week[/TD]
[TD]Median sales[/TD]
[/TR]
[TR]
[TD]Bench[/TD]
[TD]0,0,9,0,9,10,13,8,0,0,13[/TD]
[TD] ?[/TD]
[/TR]
[TR]
[TD]Seat[/TD]
[TD]2,3,2,8,2,3,4,5,0,0,0[/TD]
[TD] ?[/TD]
[/TR]
[TR]
[TD]Tennis Racket[/TD]
[TD]22,0,4,9,22,35[/TD]
[TD] ?[/TD]
[/TR]
[TR]
[TD]10L Paint[/TD]
[TD]89,101,165,50[/TD]
[TD] ?[/TD]
[/TR]
</tbody>[/TABLE]

I realize this is an old thread, but I thought this might be useful...

Assuming the comma delimited lists are in Column B, here is an array-entered** formula that should return the sough after Median...

=MEDIAN(0+TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",400)),ROW(INDIRECT("A1:A"&1+LEN(B2)-LEN(SUBSTITUTE(B2,",",""))))*400-399,400)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

 

[Aladin Akyurek]

Hello

I am really sorry for the huge delay in replying, I gave up for a while but I am still struggling with this at work.

1)
I thought I understood your clear guidelines but it still isn't working. This is the URL for the file I have attempted this on - it just fails? See Here

TinyUpload.com - best file hosting solution, with no limits, totaly free

Could somebody check it out, adjust it or whatever is required? Then re-upload for me?

2) Once that works, is it simple to say, just assess the first sat 8 numbers from the left? Or something like that?

3) Can it also ignore 0's Maybe?

Kind Regards
Mark

You had not implemented the EVAL function and you have colons in your data.

See:
https://dl.dropboxusercontent.com/u/65698317/experiment%20mark.xls