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

 

[oldbrewer]

[TABLE="width: 1208"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="4"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0,0,9,0,9,10,13,8,0,0,13[/TD]
[TD="align: right"]2[/TD]
[TD]0,9,0,9,10,13,8,0,0,13[/TD]
[TD="align: right"]2[/TD]
[TD]9,0,9,10,13,8,0,0,13[/TD]
[TD="align: right"]2[/TD]
[TD]0,9,10,13,8,0,0,13[/TD]
[TD="align: right"]2[/TD]
[TD]9,10,13,8,0,0,13[/TD]
[TD="align: right"]2[/TD]
[TD]10,13,8,0,0,13[/TD]
[TD="align: right"]3[/TD]
[TD]13,8,0,0,13[/TD]
[TD="align: right"]3[/TD]
[TD]8,0,0,13[/TD]
[TD="align: right"]2[/TD]
[TD]0,0,13[/TD]
[TD="align: right"]2[/TD]
[TD]0,13[/TD]
[TD="align: right"]2[/TD]
[TD]13[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD="align: right"]41[/TD]
[TD][/TD]
[TD="align: right"]49[/TD]
[TD][/TD]
[TD="align: right"]49[/TD]
[TD][/TD]
[TD="align: right"]49[/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]so we have 11 numbers in A2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]49[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]is the total of the numbers[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]you work out the median !!![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Markige]

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

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

I'm not sure what I did wrong implementing the code, but that is EXCELLENT thank you. I was just told the ':' was recently added to marks 6month period. Your solution works a charm.

I just have one final query to make this a total success.

'LEFT' function doesn't work in this scenario, since my sales figures vary between 1,2 & 3 characters.
What function do I need to use to truncate the last 8 numbers?

Please see my upload again and reply if possible :D

You have no idea know how much time/better data this is going to give me.

THANK YOU


Kind Regards
Mark

 

[Aladin Akyurek]

I'm not sure what I did wrong implementing the code, but that is EXCELLENT thank you. I was just told the ':' was recently added to marks 6month period. Your solution works a charm.

I just have one final query to make this a total success.

'LEFT' function doesn't work in this scenario, since my sales figures vary between 1,2 & 3 characters.
What function do I need to use to truncate the last 8 numbers?

Please see my upload again and reply if possible :D

You have no idea know how much time/better data this is going to give me.

THANK YOU


Kind Regards
Mark

Assuming 52 weeks, in C2 enter and copy down:

=REPLACE(B2,1,FIND("#",SUBSTITUTE(SUBSTITUTE(B2,":",","),",","#",44)),"")

 

[Markige]

Assuming 52 weeks, in C2 enter and copy down:

=REPLACE(B2,1,FIND("#",SUBSTITUTE(SUBSTITUTE(B2,":",","),",","#",44)),"")

I like the idea but unfortunately the sales string might only be 3 weeks long for instance if it is a new product...
I cannot change the report to enter zeros up to one year... This is a complicated one

Any more ideas? How to count from the left. It can just error if it hasn't been in stock long enough, that's not a problem!

 

[Aladin Akyurek]

I like the idea but unfortunately the sales string might only be 3 weeks long for instance if it is a new product...
I cannot change the report to enter zeros up to one year... This is a complicated one

Any more ideas? How to count from the left. It can just error if it hasn't been in stock long enough, that's not a problem!

Suppose we have the stock

77794516#c

with:

0,1,3,9,0,5

Are you trying to tell me that we should pick out say just 3 values from left (from the first 0 on) and calculate the median for:

0,1,3

Is this the correct understanding?

 

[Markige]

Suppose we have the stock

77794516#c

with:

0,1,3,9,0,5

Are you trying to tell me that we should pick out say just 3 values from left (from the first 0 on) and calculate the median for:

0,1,3

Is this the correct understanding?

Hello,

No sorry that's not what I mean. I simply want to be able to automate columns headed something like:

• Average sales for the last 1 month (say 4 weeks)
• Average sales for the last 6 months (sat 24 weeks)
• Average sales for the last 12 months (say 48 weeks)

See my latest file here 1.03 : TinyUpload.com - best file hosting solution, with no limits, totaly free

I could normally do this using standard functions, but my text is quite complicated:
E.g. a) The latest sales figure is on the left not on the right.
E.g. b) Some sales numbers have a 1 digit number, some 2 and some 3 if they sell 100 or more a week.
E.g. c) My string of numbers varies in length, according to how long we have sold the product for. This variable means I can only count from the left.


Thanks again so much for your time, I do pay it forward in things that I can do for others :D

Mark

 

[Aladin Akyurek]

Hello,

No sorry that's not what I mean. I simply want to be able to automate columns headed something like:

• Average sales for the last 1 month (say 4 weeks)
• Average sales for the last 6 months (sat 24 weeks)
• Average sales for the last 12 months (say 48 weeks)

See my latest file here 1.03 : TinyUpload.com - best file hosting solution, with no limits, totaly free

I could normally do this using standard functions, but my text is quite complicated:
E.g. a) The latest sales figure is on the left not on the right.
E.g. b) Some sales numbers have a 1 digit number, some 2 and some 3 if they sell 100 or more a week.
E.g. c) My string of numbers varies in length, according to how long we have sold the product for. This variable means I can only count from the left.


Thanks again so much for your time, I do pay it forward in things that I can do for others :D

Mark

1) I assumed MEDIAN, not AVERAGE (Adjust to suit).

2) I also used IFERROR which is not available pre-2007 systems.

3) I have slightly modified the lay-out of the results area.

C3, control+shift+enter, not just enter, copy across, and down:

=IFERROR(MEDIAN(eval("{"&LEFT(SUBSTITUTE($B3,":",",")&",",
  SEARCH("|",SUBSTITUTE(SUBSTITUTE($B3,":",",")&",",",","|",C$1))-1)&"}")),"")

Without IFERROR...

=MEDIAN(eval("{"&LEFT(SUBSTITUTE($B3,":",",")&",",
  SEARCH("|",SUBSTITUTE(SUBSTITUTE($B3,":",",")&",",",","|",C$1))-1)&"}"))

Here is the workbook that implements your request for averages involving different number of weeks:
https://dl.dropboxusercontent.com/u/65698317/Markige%201.04%20Mark%20Experiment.xls

 

[Markige]

1) I assumed MEDIAN, not AVERAGE (Adjust to suit).

2) I also used IFERROR which is not available pre-2007 systems.

3) I have slightly modified the lay-out of the results area.

C3, control+shift+enter, not just enter, copy across, and down:

=IFERROR(MEDIAN(eval("{"&LEFT(SUBSTITUTE($B3,":",",")&",",
  SEARCH("|",SUBSTITUTE(SUBSTITUTE($B3,":",",")&",",",","|",C$1))-1)&"}")),"")

Without IFERROR...

=MEDIAN(eval("{"&LEFT(SUBSTITUTE($B3,":",",")&",",
  SEARCH("|",SUBSTITUTE(SUBSTITUTE($B3,":",",")&",",",","|",C$1))-1)&"}"))

Here is the workbook that implements your request for averages involving different number of weeks:
https://dl.dropboxusercontent.com/u/65698317/Markige 1.04 Mark Experiment.xls

You deserve a medal! I am pre 2007 and you even predicted that, you beauty! I copied your option b formula and it works a treat. You might have just saved time as well as improving the accuracy of my work...

(I was about to say no no, it's wrong, then I realised it's doing exactly what a median is meant to do!)

I have another challenge for you- are you up to it? Can you add a column that says 'Median from this month last year?'

 

[Aladin Akyurek]

You deserve a medal! I am pre 2007 and you even predicted that, you beauty! I copied your option b formula and it works a treat. You might have just saved time as well as improving the accuracy of my work...

(I was about to say no no, it's wrong, then I realised it's doing exactly what a median is meant to do!)

That's absolutely great. Thanks for the kind feedback.

I have another challenge for you- are you up to it? Can you add a column that says 'Median from this month last year?'

How do you think to specify "this month last year"?

 

[oldbrewer]

he means august 2013 eg