# Need to get Median out of this list.

#### franswa3434

##### Board Regular
Hey everyone,

I need to find a way to break a report that shows the number of orders for a customer and the number of days it took to process the orders, then find a way to get the mean for each individual order. For example. if a customer has 3 orders and it took 3 days, and it to find the median of 3, 3, and 3.

To further clarify, if we had 2 customers, in which the first had 3 locations that took 3 days to process, and the second had 1 order which took 10 days, I would need to find the median of 3, 3, 3, and 10.

Below is an example of 3 customers.

This is what I am provided.
 Customer # of Orders Days to Process ABC123 3 3 XYZ321 1 10 Qwerty 2 6

<TBODY>
</TBODY>

I need to somehow come up with a way, where instead of getting a median of 3, 10, 6 (median of 6), it would actually find it as if it were listed as 3, 3, 3, 10, 6, 6 (median of 4.5).

I also need to be able to add to the list customers, orders, and their intervals to this list as my customer base grows.

Also, I can add additional sheets to this list, as well as columns. Just not rows.

Please let me know if further clarificaiton is needed, as I don't think I explained it very well.

Thanks everyone!

Last edited:

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

##### Well-known Member
All I can think of is expanding the range (sort of a variable cartesian/cross join) and then calculating the median. Quite a few macro and formula methods are available for this, here's a cool version by bosco_yip:

Last edited:

#### franswa3434

##### Board Regular
Is there a way to automatically expand the range without me having to manually do it each time an entry is added to the list (and maybe your way does this, and Im just missing it due to naivity).

##### Well-known Member
Just keep dragging the formula, and increase the 99 to a larger number if necessary.

##### Well-known Member
A macro can do this too:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Customer</td><td style="text-align: right;;"># of Orders</td><td style="text-align: right;;">Days to Process</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">ABC123</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">XYZ321</td><td style="text-align: right;;">1</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Qwerty</td><td style="text-align: right;;">2</td><td style="text-align: right;;">6</td></tr></tbody></table><p style="width:6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet5 (2)</p><br /><br />

Code:
``````Sub addrowsbyvalue()
Dim x%, y%
x = 2
Do While Cells(x, 1).Value <> ""
y = Cells(x, 2).Value
If y > 1 Then
Rows(x + 1).Resize(y - 1).Insert
With Range(Cells(x, 1), Cells(x, 3))
.Copy Destination:=.Offset(1, 0).Resize(y - 1)
End With
Else
End If
x = x + y
Loop
Cells(x + 1, 3).Formula = "=median(c2:c" & x - 1 & ")"
End Sub``````