Need to get Median out of this list.

franswa3434

Board Regular
Joined
Sep 16, 2014
Messages
69
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</SPAN>
# of Orders</SPAN>
Days to Process</SPAN>
ABC123</SPAN>
3</SPAN>
3</SPAN>
XYZ321</SPAN>
1</SPAN>
10</SPAN>
Qwerty</SPAN>
2</SPAN>
6</SPAN>

<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:

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
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:

<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 /><col /><col /><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><th>D</th><th>E</th><th>F</th><th>G</th><th>H</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><td style="text-align: right;;"></td><td style="text-align: right;;"></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><td style="text-align: right;;"></td><td style="text-align: right;;"></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><td style="text-align: right;;"></td><td style="text-align: right;;"></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;">4</td><td style=";">Qwerty</td><td style="text-align: right;;">2</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></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;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></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;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Qwerty</td><td style="text-align: right;;">1</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Qwerty</td><td style="text-align: right;;">1</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4.5</td></tr></tbody></table><p style="width:3.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</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F2</th><td style="text-align:left">=LOOKUP(<font color="Blue">ROW(<font color="Red">A1</font>),SUMIF(<font color="Red">OFFSET(<font color="Green">B$1,,,ROW(<font color="Purple">$1:$99</font>),</font>),"<>"</font>)+1,A$2:A$99</font>)&""</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G2</th><td style="text-align:left">=VALUE(<font color="Blue">LOOKUP(<font color="Red">ROW(<font color="Green">B1</font>),SUMIF(<font color="Green">OFFSET(<font color="Purple">C$1,,,ROW(<font color="Teal">$1:$99</font>),</font>),"<>"</font>)+1,B$2:B$99</font>)&""</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H2</th><td style="text-align:left">=VALUE(<font color="Blue">LOOKUP(<font color="Red">ROW(<font color="Green">C1</font>),SUMIF(<font color="Green">OFFSET(<font color="Purple">B$1,,,ROW(<font color="Teal">$1:$99</font>),</font>),"<>"</font>)+1,C$2:C$99</font>)&""</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H9</th><td style="text-align:left">=MEDIAN(<font color="Blue">H2:H7</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

franswa3434

Board Regular
Joined
Sep 16, 2014
Messages
69
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).
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
Just keep dragging the formula, and increase the 99 to a larger number if necessary.
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
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
<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=";">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;">4</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;">5</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;">6</td><td style=";">Qwerty</td><td style="text-align: right;;">2</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Qwerty</td><td style="text-align: right;;">2</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4.5</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 /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C9</th><td style="text-align:left">=MEDIAN(<font color="Blue">C2:C7</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,090,490
Messages
5,414,843
Members
403,548
Latest member
frostinheart

This Week's Hot Topics

Top